4

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

0

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

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

  • 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 9 '17 at 5:41
4

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.

0

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 SELECTs, you can drop the two left joins and the GROUP BY – RDFozz Jun 12 '18 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.