SELECT (
SELECT tt.username
FROM users tt
WHERE tt.id = aa.ref
) as kullanici , COUNT(*) as sayi FROM users as aa WHERE aa.ref!='0' GROUP BY kullanici