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
