Joins with aggregate data

[Paul McGarry <paul.mcgarry@xxxxxxxxx>]

Hi there everyone,

I'm having trouble getting the data I want from my tables.
Here is a simplified version of my tables:

======
create table lefty (
 day date,
 good numeric(10,2),
 grp integer
);

insert into lefty values ('2005-06-01',5.00,1);
insert into lefty values ('2005-06-02',10.00,1);
insert into lefty values ('2005-06-01',2.50,2);


create table righty (
 day date,
 bad numeric(10,2),
 grp integer
);

insert into righty values ('2005-06-01',-5.00,3);
insert into righty values ('2005-06-02',-10.00,1);
insert into righty values ('2005-06-02',-2.50,1);
======

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)

At first I thought:
======
SELECT grp, count(goodamount), sum(goodamount), count(badamount), sum(badamount)
FROM lefty FULL OUTER JOIN righty USING (grp)
GROUP BY grp;
======
might do it but the join happens before the aggregation and the grp 1
results match each other two ways in the join and thus get counted
twice:
======
 grp | count |  sum  | count |  sum
-----+-------+-------+-------+--------
   3 |     0 |       |     1 |  -5.00
   2 |     1 |  2.50 |     0 |
   1 |     4 | 30.00 |     4 | -25.00
======

Can someone point me in the right direction?


Paul

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org