Full outer join question.

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

On Fri, Jul 08, 2005 at 11:45:59AM +1000, Paul McGarry wrote:

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)

How about doing the aggregates in separate subqueries and then doing
the outer join? Something like this:

SELECT coalesce(g.grp, b.grp) AS grp,
coalesce(g.count, 0) AS countgood,
coalesce(g.sum, 0) AS sumgood,
coalesce(b.count, 0) AS countbad,
coalesce(b.sum, 0) AS sumbad
FROM
(SELECT grp, count(good), sum(good) FROM lefty GROUP BY grp) AS g
FULL OUTER JOIN
(SELECT grp, count(bad), sum(bad) FROM righty GROUP BY grp) AS b USING (grp);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Thank you Michael, your suggestion works a charm (though I didn't
bother coalescing the two grp because I think the USING takes care of
that anyway.

Paul

On 7/8/05, Michael Fuhr <mike@fuhr.org> wrote:

On Fri, Jul 08, 2005 at 01:37:54PM +1000, Paul McGarry wrote:

Thank you Michael, your suggestion works a charm (though I didn't
bother coalescing the two grp because I think the USING takes care of
that anyway.

Oops, yeah -- I had started with "ON b.grp = g.grp" but changed it
to "USING (grp)" and neglected to get rid of that coalesce.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Am Freitag, 8. Juli 2005 03:22 schrieb Paul McGarry:

When I do that I lose all the rows whose grp isn't in both tables. For
example: ======
SELECT grp, count(goodamount), sum(goodamount), count(badamount),
sum(badamount) FROM lefty FULL OUTER JOIN righty USING (grp)
WHERE lefty.day >= '2005-06-01' AND righty.day>='2005-06-01'
GROUP BY grp;

You need to write ... WHERE (lefty.day >= '2005-06-01' OR lefty.day IS NULL)
AND etc.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/