MySQL Aggregation of Multiple Left Joins

Question

Let's say I have two tables - games and actions - and I want the output as shown in the third table.

games
+----+---------+
| id |  title  |
+----+---------+
|  1 | "one"   |
|  2 | "two"   |
|  3 | "three" |
+----+---------+

actions
+----+---------+------+---------+
| id | game_id | type | user_id |
+----+---------+------+---------+
|  1 |       1 |    1 |       2 |
|  2 |       2 |    1 |       2 |
|  3 |       3 |    1 |       2 |
|  4 |       1 |    2 |       1 |
|  5 |       1 |    2 |       2 |
|  6 |       1 |    2 |       3 |
|  7 |       1 |    3 |       1 |
|  8 |       1 |    3 |       1 |
|  9 |       1 |    3 |       3 |
| 10 |       2 |    3 |       1 |
| 11 |       2 |    3 |       1 |
+----+---------+------+---------+

expected output for game_aggregates
+----+---------+------------------+--------------------+
| id |  title  | n_actions_type_3 | n_actions_type_all |
+----+---------+------------------+--------------------+
|  1 | "one"   |                3 |                  7 |
|  2 | "two"   |                2 |                  3 |
|  3 | "three" |                0 |                  1 |
+----+---------+------------------+--------------------+

I can start with a simple

select 
    g.*, 
    count(a3.id) as n_actions_type_3 
from games g
left join actions a3
    on g.id = a3.game_id and a3.type = 3
group by g.id;

and I'll get.

+----+---------+------------------+
| id |  title  | n_actions_type_3 |
+----+---------+------------------+
|  1 | "one"   |                3 |
|  2 | "two"   |                2 |
|  3 | "three" |                0 |
+----+---------+------------------+

So far, so good.

Now, if I add multiple LEFT JOINS - it doesn't work - the numbers are very different:

-- doesn't result in correct counts
select 
    g.*, 
    count(a3.id) as n_actions_type_3,
    count(a_all.id) as n_actions_type_all, 
from games g
left join actions a3
    on g.id = a3.game_id and a3.type = 3
left join actions a_all
    on g.id = a_all.game_id    
group by g.id;

One way is to wrap the earlier result on n_actions_type_3 in a select statement, and then do a left join for n_actions_type_all - but that'll be doing just one left join at a time.

Is it possible to get the desired output using multiple left joins ?

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Sep 20 '20 at 05:38
This involves a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregations over appropriate rows and/or aggregate a case statement picking rows; join on a unique column set. Sometimes DISTINCT aggregation picks the right values after a non-key join. [sum data from multiple tables](https://stackoverflow.com/q/2591390/3404097) — philipxy, Sep 20 '20 at 07:35

score 2 · Accepted Answer · answered Sep 20 '20 at 04:22

I would suggest left joining the games table to a subquery which finds the two aggregates you want for each game:

SELECT
    g.id,
    g.title,
    a.n_actions_type_3,
    a.n_actions_type_all
FROM games g
LEFT JOIN
(
    SELECT game_id,
           SUM(type = 3) AS n_actions_type_3,
           COUNT(*) AS n_actions_type_all
    FROM actions
    GROUP BY game_id
) a
    ON a.game_id = g.id;

MySQL Aggregation of Multiple Left Joins

1 Answers1

Demo