Join Multiple Tables for Aggregates

Question

How do I join multiple tables for aggregates with Sum and Count functions?

The query I'm trying is as below:

Select
  campaigns.id,
  campaigns.name,
  Count(landers.campaign_id) As landers_count,
  Sum(conversions.revenue) As total_revenue
From
  campaigns Left Join
  conversions
    On campaigns.id = conversions.campaign_id Left Join
  landers
    On campaigns.id = landers.campaign_id
Group By
  campaigns.id

I even tried Outer joins but no luck and I'm getting inaccurate results.

My example tables are as below:

Campaigns table:

| id | name           |
+----+----------------+
| 1  | Facebook Ads   |
| 2  | Bing Ads       |
| 3  | Direct Mailing |
| 4  | Solo Ads       |

Landers table:

| id | name        | campaign_id |
+----+-------------+-------------+
| 1  | Lander 1    | 1           |
| 2  | Lander Two  | 2           |
| 3  | Lander 3    | 4           |
| 4  | Lander Four | 1           |

Conversions table:

| id | revenue | campaign_id | lander_id |
+----+---------+-------------+-----------+
| 1  | 25.00   | 1           | 1         |
| 2  | 12.00   | 1           | 4         |
| 3  | 19.00   | 4           | 3         |

The results I'm expecting should look like below:

| campaigns.id | campaigns.name | landers_count | total_revenue |
+--------------+----------------+---------------+---------------+
| 1            | Facebook Ads   | 2             | 37.00         |
| 2            | Bing Ads       | 1             | 00.00         |
| 3            | Direct Mailing | 0             | 00.00         |
| 4            | Solo Ads       | 1             | 19.00         |

Fiddle based on answer from @'Willem Renzema'

Fiddle

score 6 · Answer 1 · answered Feb 08 '19 at 23:39

This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused.

A campain has landers and conversions. If we merely join all tables, we get for a campaign with two landers and three conversions 2 x 3 = 6 result rows. If we sum or count then, we'll get wrong results (the number of landers will be three-fold in the example and the conversion sum will be doubled).

There are mainly two ways to go about this:

Aggregate in subqueries in the select clause.

select
  id, name,
  (select count(*) from landers l where l.campaign_id = ca.id) as landers_count,
  (select sum(revenue) from conversions co where co.campaign_id = ca.id) as total_revenue
from campaigns ca
order by id;

Aggregate before joining.

select ca.id, ca.name, l.landers_count, co.total_revenue
from campaigns ca
left join
(
  select campaign_id, count(*) as landers_count
  from landers
  group by campaign_id
) l on l.campaign_id = ca.id
left join
(
  select campaign_id, sum(revenue) as total_revenue
  from conversions
  group by campaign_id
) co on co.campaign_id = ca.id
order by ca.id;

You can use COALESCE to get zeros instead of nulls in your result.

Willem Renzema · Accepted Answer · 2019-08-04T14:39:00.360

0

This answer was incorrect, but I can't delete accepted answers.

Instead, see this other answer to this question for a solution.

edited Aug 04 '19 at 14:39

answered Jun 08 '17 at 18:45

Willem Renzema

1,814
1
9
12

I will accept this answer as it's working well with my sample data. However while trying the same on my production data, for some reason I'm always getting the "landers_count" from the conversions table. E.g if I'm having only 3 landers in landers table but if a particular lander is having 50 conversion then I'm getting 50 as the landers count in final results – Jani Jun 09 '17 at 05:41

score 0 · Answer 3 · answered Jun 12 '18 at 15:08

To make right calculation the jointure should be like this:

Select   campaigns.id,   campaigns.name,   (SELECT Count(landers.campaign_id)FROM landers WHERE campaigns.id = landers.campaign_id) As landers_count,   (SELECT Sum(conversions.revenue) FROM conversions WHERE campaigns.id = conversions.campaign_id ) As total_revenue From   campaigns Left Join   conversions
    On campaigns.id = conversions.campaign_id Left Join   landers
    On campaigns.id = landers.campaign_id Group By   campaigns.id

Actually, with the two embedded `SELECT`s, you can drop the two left joins and the `GROUP BY` — RDFozz, Jun 12 '18 at 16:07

Join Multiple Tables for Aggregates

3 Answers3

Aggregate in subqueries in the select clause.

Aggregate before joining.