Making a MySQL COUNT query

Question

after 4 days of tries and researchs over the web I can't make my wishes working.
Any help would be appreciated !
Performed on MYSQL WORKBENCH
I got a DB made of 3 tables :

user	Posts	likes
username	id	id
id	title	userId (foreign key from user.id)
email	content	postId (foreign key from posts.id)
password	date
image	imageUrl
	userId (foreign key from user.id)

I want a query that would take specifics datas from USER and POSTS plus a total count of likes for each post ordered by id of post and a total result of 0 if no likes exist with this id. Here the list of what I want my query to do :

• Select specifics datas in USER and POSTS; ( working )
• COUNT the total of LIKES for each POSTS and give me a total of 0 if any post has no likes;

My Actual Query :

SELECT P.id, P.userId, P.title, P.content, P.date, P.imageUrl, U.username, U.permission, U.image,
COUNT(CASE WHEN L.postId = P.id THEN 1 ELSE NULL END) AS totalLikes
FROM posts AS P
LEFT JOIN user AS U
ON U.id = P.userId
LEFT JOIN likes AS L
ON P.id = L.postId
ORDER BY P.id DESC;

This is returning only 1 post with a count of all the likes in database so not what i'm expecting :-(

Answer 1

Do the aggregation inside likes only and then join the other tables to the resultset of the aggregation:

SELECT P.id, P.userId, P.title, P.content, P.date, P.imageUrl, 
       U.username, U.permission, U.image,
       COALECE(L.totalLikes, 0) AS totalLikes
FROM posts AS P
INNER JOIN user AS U ON U.id = P.userId  -- change to LEFT JOIN if the column userId in posts is nullable
LEFT JOIN (
  SELECT postId, COUNT(*) AS totalLikes
  FROM likes 
  GROUP BY postId
) AS L ON L.postId = P.id
ORDER BY P.id DESC;