How to group summed up columns from different tables?

Question

I have a user table (User) and 3 tutorial tables (Text, Video and Other).

Each tutorial has the columns rating_positive and rating_negative and is linked to a user (id).

I want to select the 10 users with the most tutorials and the sum of positive/negative ratings of their tutorials.

I tried the following query but it does not work. It returns way too many results for tutorials_count/pos/neg. How can I do it correctly?

SELECT  
    u.id AS user_id,  
    (COUNT(t.id) + COUNT(v.id) + COUNT(o.id)) AS tutorials_count,
    (SUM(t.rating_positive) + SUM(v.rating_positive) + SUM(o.rating_positive)) AS pos,
    (SUM(t.rating_negative) + SUM(v.rating_negative) + SUM(o.rating_negative)) AS neg
FROM
    user u LEFT JOIN trick t ON u.id = t.submitter_id
    LEFT JOIN video v ON u.id = v.submitter_id
    LEFT JOIN other o ON u.id = o.submitter_id
GROUP BY u.id
ORDER BY tutorials_count DESC
LIMIT 10

Answer 1

Try making a subquery with a UNION ALL of the three tables you are interested in and then join with that:

SELECT  
    u.id AS user_id,  
    COUNT(submitter_id) AS tutorials_count,
    IFNULL(SUM(rating_positive), 0) AS pos,
    IFNULL(SUM(rating_negative), 0) AS neg
FROM user u
LEFT JOIN (
    SELECT submitter_id, rating_positive, rating_negative FROM trick
    UNION ALL
    SELECT submitter_id, rating_positive, rating_negative FROM video
    UNION ALL
    SELECT submitter_id, rating_positive, rating_negative FROM other
) T1
ON u.id = T1.submitter_id
GROUP BY u.id
ORDER BY tutorials_count DESC
LIMIT 10

Answer 2

The LEFT JOINs are fine, and will perform better than unioning all three tables before performing aggregation.

The issue is that SUMmation on a LEFT JOIN means the result could be NULL, which you can't add in conjunction with the sum from the other columns. IE:

... SUM(t.rating_positive) + 1

...will return NULL if SUM(t.rating_positive) there are no supporting records, because NULL + 1 equals NULL.

You need to use COALESCE to convert these to zero for the math to work - IFNULL is an acceptable alternative, but it's MySQL specific so not likely to be portable:

   SELECT u.id AS user_id,  
          COALESCE(COUNT(t.id), 0) + COALESCE(COUNT(v.id), 0) + COALESCE(COUNT(o.id), 0) AS tutorials_count,
          COALESCE(SUM(t.rating_positive), 0) + COALESCE(SUM(v.rating_positive), 0) + COALESCE(SUM(o.rating_positive), 0) AS pos,
          COALESCE(SUM(t.rating_negative), 0) + COALESCE(SUM(v.rating_negative), 0) + COALESCE(SUM(o.rating_negative), 0) AS neg
     FROM USER u 
LEFT JOIN trick t ON u.id = t.submitter_id
LEFT JOIN video v ON u.id = v.submitter_id
LEFT JOIN other o ON u.id = o.submitter_id
 GROUP BY u.id
 ORDER BY tutorials_count DESC
    LIMIT 10