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'
Best Answer
This answer was incorrect, but I can't delete accepted answers.
Instead, see this other answer to this question for a solution.