SQL get aggregate breakdown by joined table

Question

I have the following entities and relationships in a MySQL database:

• Each Post has N Review
• Each Review has 1 Comment and a state (is_accepted)
• Each Comment has 1 User

Desired result:

I'm trying to get an aggregate report of reviews on a specific post, grouped by user:

+---------+--------------+-----------------------+---------------------+
| user_id | review_count | review_accepted_count | review_denied_count |
+---------+--------------+-----------------------+---------------------+
| 1       | 3            | 2                     | 1                   |
| 2       | 5            | 1                     | 4                   |
| 3       | 1            | 1                     | 0                   |
+---------+--------------+-----------------------+---------------------+

What I've tried:

SELECT
    C.user_id,
    COUNT(C.user_id) AS review_count,
    (SELECT COUNT(*) FROM reviews WHERE `post_id` = R.post_id AND `user_id` = C.user_id AND `is_accepted` = 1) review_accepted_count,
    (SELECT COUNT(*) FROM reviews WHERE `post_id` = R.post_id AND `user_id` = C.user_id AND `is_accepted` = 0) review_denied_count
FROM reviews R
INNER JOIN comments C ON C.id = R.comment_id
WHERE post_id = 1234
GROUP BY C.user_id

Actual result:

The returned review_accepted_count and review_denied_count columns are the total across all reviews, not grouped per user

Answer 1

Try this:

SELECT
    C.user_id,
    COUNT(C.user_id) AS review_count,
    SUM(CASE WHEN `is_accepted` = 1 THEN 1 ELSE 0 END) AS review_accepted_count,
    SUM(CASE WHEN `is_accepted` = 0 THEN 1 ELSE 0 END) AS review_denied_count
FROM reviews R
INNER JOIN comments C ON C.id = R.comment_id
WHERE post_id = 1234
GROUP BY C.user_id

In your subqueries review_accepted_count and review_denied_count you should join (in WHERE clause) by the review primary key. You don't need to make subqueries to get the result. This way is faster.

If you only have 1s and 0s in column is_accepted you can do:

SUM(`is_accepted`) AS review_accepted_count