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 :-(

It has no added value to add Workbench. This is about a SQL query, and it really does not matter which client you use. MySQLworkbench is just a client. — Luuk
– Luuk, Commented Apr 29, 2022 at 7:31

forpas · Accepted Answer · 2022-04-28 15:32:06Z

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;

answered Apr 28, 2022 at 15:32

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Making a MySQL COUNT query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related