CODEWITHSUNDEEP
mysqlselectjoingroup-byaggregate-functions
Code Prank
Code Prank

Reputation: 4250

MySQL sum, count with group by and joins

I have three tables types, post and insights.

Here is the link to my sql fiddle SQL Fiddle.

Now i want to generate a report which contains number of post against each type and the sum of their likes and comments i.e. Type | COUNT(post_id) | SUM(likes) | SUM(comments).

These are my tries:

select type_name, count(p.post_id), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join insights i on p.post_id = i.post_id
group by type_name;

Result: Aggregate values are not correct.

select type_name, count(p.post_id), p.post_id, 
  (select sum(likes) from insights where post_id = p.post_id) as likes, 
  (select sum(comments)from insights where post_id = p.post_id) as comments
from types t
left join posts p on t.type_id = p.post_type
group by type_name;

Result: Displays the sum of likes and comments of only one post.

Upvotes: 4

Views: 1882

Answers (2)

bavo
bavo

Reputation: 507

try this

SELECT types.type_name, stats.posts, stats.likes, stats.comments
FROM types
LEFT JOIN (
select post_type, count(i.post_id) as posts, sum(i.likes) as likes, sum(i.comments) as comments
from insights i INNER JOIN posts p ON i.post_id = p.post_id
) as stats
ON types.type_id = stats.post_type;

Upvotes: -1

Barmar
Barmar

Reputation: 781721

Your first attempt was real close. But each post_id was being multiplied by the number of matches in insights, so you need to use DISTINCT:

select type_name, count(distinct p.post_id), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join insights i on p.post_id = i.post_id
group by type_name;

Alternatively, you can group with a subquery that combines all the insights for the same post:

select type_name, count(*), sum(likes), sum(comments)
from types t
left join posts p on t.type_id = p.post_type
left join (select post_id, sum(likes) likes, sum(comments) comments
           from insights
           group by post_id) i on p.post_id = i.post_id
group by type_name;

FIDDLE

Upvotes: 3

Related Questions