Join Multiple Tables for Aggregates
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.