sql - MySql: how to make subquery and count all rows where id is the same in two tables

Question

How to make a query which return values for specific ID's not for all.

    SELECT content.id, count(case likes.type when 'p' then 1 else null end) as p
FROM content
JOIN likes on likes.content_id = content.id

this code returns:

ID p
1 18

but i want it:

ID p
1 12
2 4
3 2

Answer 1

Add a group by

SELECT content.id, 
       sum(likes.type = 'p') as p
FROM content
JOIN likes on likes.content_id = content.id
GROUP BY content.id

Then the aggregate functions (i.e. count()) are applied to the groups and not to the whole result.