SQL (MySQL) join, sum and count simultaneously

Question

I need a query with posts-id, posts-title, posts-created_at, users-name, number-of-comments and likes-status. likes-status is 0 if none exists. Like this:

id |title   |created_at |name  |status  |commentscount |
========================================================
1  |Hello 1 |2015-07-22 |Baker |-2      |2             |
2  |Hallo 2 |2015-07-23 |Tom   |0       |0             |

My attempt:

SELECT p.id, p.title, p.created_at, u.name, COALESCE(c.body, 0) as commentscount, COALESCE(sum(l.status), 0) as status
FROM posts p
LEFT OUTER JOIN likes l ON l.post_id = p.id
LEFT OUTER JOIN users u ON u.id = p.user_id
LEFT OUTER JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title, p.created_at, u.name, c.body

Result: id 1 should be summarized and commentscount should be 2. Status -2 should remain.

id |title   |created_at |name  |status  |commentscount |
========================================================
1  |Hello 1 |2015-07-22 |Baker |-2      |Comment 1     | << issue
1  |Hello 1 |2015-07-22 |Baker |-2      |Comment 2     | << issue
2  |Hallo 2 |2015-07-23 |Tom   |0       |0

users table

id |name  |email             |password |created_at |
====================================================
1  |Baker |[email protected] |UHds(&   |2015-07-20 |
2  |Tom   |[email protected]   |ihj=)?   |2015-07-21 |

posts table

id |user_id |title   |created_at |
==================================
1  |1       |Hello 1 |2015-07-22 |
2  |2       |Hello 2 |2015-07-23 |

likes table

id |user_id |post_id |status |created_at  |
===========================================
1  |1       |1       |-1      |2015-07-24 |
2  |2       |1       |-1      |2015-07-25 |

comments table

id |user_id |post_id |body      |created_at  |
==============================================
1  |1       |1       |Comment 1 |2015-07-28  |
2  |2       |1       |Comment 2 |2015-07-28  |

See also: SQL join, sum, group-by and instead of null 0

Try changing "COALESCE(c.body, 0) as commentscount" for "Count(c.body) as commentscount" and remove commentscount from the group by clause. :) — TomDillinger
– TomDillinger, Commented Jul 27, 2015 at 22:20
I've already tried this. The problem is, just as an example, if i have for id 1 two rows with status 1 and commentscount 2. Then i get for status 2 and commentscount 4. — baker
– baker, Commented Jul 27, 2015 at 22:56
I'm not sure I understand the question. Could you add sample data that reflects this proposed scenario? — TomDillinger
– TomDillinger, Commented Jul 27, 2015 at 23:25
See my attempts result and imagine. If I have 5 rows with id 1... Hello 1, Hello 1, Hello 1, Hello 1, Hello 1... Baker, Baker, Baker, Baker, Baker... for status 1, 1, 1, 1, 1. If I remove commentscount respectively c.body from group by. Than I get for status 5 instead of 1. Before 5 rows width status 1, after 1 row but with status 5. Status must be 1. — baker
– baker, Commented Jul 28, 2015 at 8:46

eirikir · Accepted Answer · 2015-07-28 00:02:27Z

1

You can join to subqueries with just the counts you need. This will speed up your query and eliminate the need to GROUP BY in the outer clause:

SELECT p.id, p.title, p.created_at, u.name, COALESCE(c.comments_count, 0) AS comments_count, COALESCE(l.status_sum, 0) AS status_sum 
  FROM posts p 
  LEFT OUTER JOIN users u ON u.id = p.user_id
  LEFT OUTER JOIN (
    SELECT l.post_id, SUM(l.status) AS status_sum 
    FROM likes l 
    GROUP BY l.post_id
  ) l ON l.post_id = p.id
  LEFT OUTER JOIN (
    SELECT c.post_id, COUNT(*) as comments_count 
    FROM comments c 
    GROUP BY c.post_id
  ) c ON c.post_id = p.id

answered Jul 28, 2015 at 0:02

eirikir

3,8623 gold badges24 silver badges39 bronze badges

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

2 Comments

baker Over a year ago

Thank you! This works but are you sure that subselect forms better than join?

eirikir Over a year ago

@baker I'm not sure what you mean by "forms better"? I'm pretty confident that this method would be faster for large data sets, but you'd have to benchmark it against a particular data set to be sure.

user4621032 · Accepted Answer · 2015-07-28 00:29:26Z

1

SELECT 
 COUNT(p.id), p.title, p.created_at, u.name, COUNT(c.body) as commentscount,
 IFNULL(sum(l.status),0) as status
  FROM posts p
    LEFT JOIN likes l ON l.post_id = p.id
    LEFT JOIN users u ON u.id = p.user_id
    LEFT JOIN comments c ON c.post_id = p.id
 GROUP BY p.title

edited Jul 28, 2015 at 0:29

answered Jul 27, 2015 at 23:02

user4621032

6411 gold badge5 silver badges11 bronze badges

3 Comments

baker Over a year ago

No, unfortunately not. See what I have written: "id 1 should be summarized". I've already tried this too.

user4621032 Over a year ago

do you mean 1+1 and 2+2 or counted ----SUM(p.id)

baker Over a year ago

There are the same records. Only c.body is different. c.body must be counted. And i need 1 row. See my attempts result.

Collectives™ on Stack Overflow

SQL (MySQL) join, sum and count simultaneously

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related