CODEWITHSUNDEEP
mysqlsqljoin
Raphael Jeger
Raphael Jeger

Reputation: 5222

MySQL join on one table

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.

Upvotes: 0

Views: 41

Answers (2)

Bere
Bere

Reputation: 1747

SELECT recommendedByUserId, COUNT(*) as cnt
FROM users us 
group by recommendedByUserId order by cnt desc;

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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

Upvotes: 3

Related Questions