How to count things from different tables and group it by user?

Question

I have those 3 tables enter image description here and i don't know how to connect them to have a users.id, username, number of likes and number of comments by each user. I want to have it in 4 columns.

I tried this: select u.id, u.username, count(l.photo_id) as posts_liked from users u left join likes l on u.id = l.user_id group by u.id UNION ALL select u.id, u.username, count(c.user_id) as com_written from users u left join comments c on u.id = c.user_id group by u.id; but the result is just 3 tables with number of comments in a column with number of likes.

jnisen · Accepted Answer · 2024-01-11 13:48:27Z

0

I join the three tables and then grouped by id. Try this if it answer your question

SELECT 
    u.id, 
    u.username, 
    count(l.photo_id) as nro_likes,
    count(c.photo_id) as nro_comments
FROM users u
LEFT JOIN comments c
ON u.id = c.id
INNER JOIN likes l
ON c.user_id = l.user_id
GROUP BY u.id

edited Jan 11, 2024 at 13:48

answered Jan 11, 2024 at 11:16

jnisen

643 bronze badges

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

2 Comments

thiagissimo Over a year ago

I have already tried this and it's wrong. It shows the same number of likes and the same number of comments.

jnisen Over a year ago

can you show the tables to help you and the desire output? I just use an example for that

Collectives™ on Stack Overflow

How to count things from different tables and group it by user?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related