Mysql: How To Make Subquery And Count All Rows Where Id Is The Same In Two Tables

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 l

Solution 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.

Solution 2:

You query would fail in most databases. The problem is that content.id is not summarized in the select but you are using an aggregation function.

This is a simple problem to fix:

SELECT content.id, count(case likes.type when'p'then1elsenullend) as p
FROM content
JOIN likes on likes.content_id = content.id
GROUPBY content.id;

However, in general, you should be careful and always include all non-aggregated columns in the select in a group by.