Malii Alii Malii Alii - 3 months ago 10
SQL Question

GROUP BY using another table

I have two tables users and votes and some users can have same

unq_id


I'm trying to select all distinct
unq_id
from users and sort them by
votes
from votes table.

But the results are not how I expected, the votes are duplicated. You can see in below image

enter image description here

SELECT DISTINCT u.unq_id, COUNT(v.id) AS count_votes
FROM users u
INNER JOIN votes v
ON u.unq_id = v.unq_id
WHERE u.unq_id <> ''
GROUP BY u.unq_id;


SQLFiddle: http://sqlfiddle.com/#!9/e0bb35/1

Expected results:

3fyx6 - 3
9kx9mq - 1

Answer

To get the desired result you don't need to join to users, but based on your comment "some users have no votes at all and i want to put them on the list" you should aggregate before a LEFT join:

SELECT DISTINCT
   u.unq_id, 
   COALESCE(v.count_votes, 0)
FROM users u
LEFT JOIN
 ( select unq_id, count(*) as count_votes
   from votes
   group by unq_id
 ) v
ON u.unq_id = v.unq_id
WHERE u.unq_id <> '';