Re: Joins with aggregate data

On Fri, Jul 08, 2005 at 11:45:59AM +1000, Paul McGarry wrote:
> I basically want a query which will give me:
> ======
>  grp | count(good) |  sum(good)  | count(bad) |  sum(bad)
> -----+-------------+-------------+------------+----------
>    3 |           0 |             |          1 |   -5.00
>    2 |           1 |        2.50 |          0 |
>    1 |           2 |       15.00 |          2 |  -12.50
> ======
> (possibly with zeros rather than nulls but doesn't matter)

How about doing the aggregates in separate subqueries and then doing
the outer join?  Something like this:

SELECT coalesce(g.grp, b.grp) AS grp,
       coalesce(g.count, 0) AS countgood,
       coalesce(g.sum, 0) AS sumgood,
       coalesce(b.count, 0) AS countbad,
       coalesce(b.sum, 0) AS sumbad
FROM
 (SELECT grp, count(good), sum(good) FROM lefty GROUP BY grp) AS g
FULL OUTER JOIN
 (SELECT grp, count(bad), sum(bad) FROM righty GROUP BY grp) AS b USING (grp);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/