Re: Problem JOINing tables correctly

[Peter Brawley]

Across joins, aggregation multiplies values as if it were the sorcerer's apprentice, so such aggregations need to be tucked inside subqueries, so a query like ...

select a.id, sum(b.x), sum(c.y)
from a join b on a.id=b.id
join c on a.id=c.id
where ...
group by id;

needs to become ...

select a.id, bsum.sumx, csum.sumy
from a
join (
  select b.id,sum(x) as sumx 
  from b 
  where ...
  group by id
) as bsum using(id)
join (
  select c.id,sum(y) as sumy 
  from c 
  where ...
  group by id
) as csum using(id);