I have a users-table which is like (shorted):
id (BIGINT)
username (VARCHAR)
recommendedByUserId (BIGINT)
In recommendedByUserId the ID of the recommender is stored, which is the users.id-value of the recommender.
Now I need to know how many times each users.id is in users.recommendedByUserId and sort on them descending, so the user with the most recommendations is on top of the result.
I tried:
SELECT u.username, COUNT(r.id) FROM users u INNER JOIN users r ON u.id = r.recommendedByUserId
but that does not work.
If the id is sufficient
SELECT recommendedByUserId, COUNT(*) as rec_cnt
FROM users u
group by recommendedByUserId
order by rec_cnt desc
If you also need the name then you can do
select u.username, x.rec_cnt
from users u
inner join
(
SELECT recommendedByUserId as id, COUNT(*) as rec_cnt
FROM users u
group by recommendedByUserId
) x on x.id = u.id
order by x.rec_cnt desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments