Joins with aggregate data
| От | Paul McGarry |
|---|---|
| Тема | Joins with aggregate data |
| Дата | |
| Msg-id | a056b1d405070718456f280a48@mail.gmail.com обсуждение исходный текст |
| Ответ на | Full outer join question. (Paul McGarry <paul.mcgarry@gmail.com>) |
| Ответы |
Re: Joins with aggregate data
|
| Список | 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
В списке pgsql-general по дате отправления: