CODEWITHSUNDEEP
mysql
Eelco Luurtsema
Eelco Luurtsema

Reputation: 1199

MySQL: Join to many: select sums in both tables

I have the following problem. I want to join a table in a query. This table has multiple records. Now, I want the sum of a column in the main table, and the sum of a column in the joined table. This leads to problems, because the sum of the column in the main table is now incorrect (because I join to multiple rows).

| id | date       | campaign_id | sessions |
|----|------------|-------------|----------|
| 1  | 2019-07-01 | 1           | 10       |
| 2  | 2019-07-02 | 1           | 20       |

Joined table:

| id | some_var | campaign_id | some_other_metric |
|----|----------|-------------|-------------------|
| 1  | a        | 1           | 100               |
| 2  | b        | 1           | 200               |
| 3  | c        | 1           | 300               |

Query:

select table_a.campaign_id, SUM(table_a.sessions), SUM(table_b.some_other_metric)
FROM table_a
left join table_b ON table_a.campaign_id = table_b.campaign_id)
group by table_a.campaign_id

I am looking for the following result:

| campaign_id | SUM(table_a.sessions) | SUM(table_b.some_other_metric) |
|-------------|-----------------------|--------------------------------|
| 1           | 30                    | 600                            |

SUM(table_a.sessions) = 30
SUM(table_b.some_other_metric) = 600

But I am getting other results:

SUM(table_a.sessions) = 90
SUM(table_b.some_other_metric) = 1200

Is there a different way to join this tables to get the result?

Thanks in advance.

Upvotes: 1

Views: 45

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

One straightforward approach is to use separate subqueries to aggregate the sums in each of the two tables:

SELECT
    a.campaign_id,
    COALESCE(a.sessions_sum, 0) AS session_sum,
    COALESCE(b.other_sum, 0) AS other_sum
FROM
(
    SELECT campaign_id, SUM(sessions) AS sessions_sum
    FROM table_a
    GROUP BY campaign_id
) a
LEFT JOIN
(
    SELECT campaign_id, SUM(some_other_metric) AS other_sum
    FROM table_b
    GROUP BY campaign_id
) b
    ON a.campaign_id = b.campaign_id;

The above approach has the potential drawback that any campaign_id which appears only in table_b would be dropped from the result set. But, it is no worse than your current left join approach. Ideally, you could use a third table which contains all campaign_id values which you want to appear in the result set.

Upvotes: 3

Related Questions