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: | Raw Message | Whole Thread | 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. |