| From: | Paul McGarry <paul(dot)mcgarry(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Joins with aggregate data | 
| Date: | 2005-07-08 01:45:59 | 
| Message-ID: | a056b1d405070718456f280a48@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Fuhr | 2005-07-08 02:56:48 | Re: Joins with aggregate data | 
| Previous Message | Paul McGarry | 2005-07-08 01:22:39 | Full outer join question. |