Screwy behavior with SUM and multiple joins to same table - Mailing list pgsql-general

Screwy behavior with SUM and multiple joins to same table:

__START SCRIPT__

drop table product;
create table product (prod varchar, name varchar);
insert into product (prod, name) values ('A', 'Cat Food');
insert into product (prod, name) values ('B', 'Dog Food');

drop table sales;
create table sales (prod varchar, store integer, units integer);
insert into sales (prod, store, units) values ('A', 1, 50);
insert into sales (prod, store, units) values ('A', 2, 100);


\echo
\echo cat food in store 1:
select   p.prod
       , sum(s.units) as store_1
from     product p
       , sales s
where    p.prod = s.prod and store=1 and p.prod='A'
group by p.prod;

\echo
\echo cat food in store 2:
select   p.prod
       , sum(s.units) as store_2
from     product p
       , sales s
where    p.prod = s.prod and store=2 and p.prod='A'
group by p.prod;

\echo
\echo cat food in stores 1 & 2:
select   p.prod
       , sum(s1.units) as store_1
       , sum(s2.units) as store_2
       , sum(sAll.units) as store_All
from     product p
       , sales s1
       , sales s2
       , sales sAll
where    p.prod = s1.prod and s1.store=1
     and p.prod = s2.prod and s2.store=2
     and p.prod = sAll.prod and sAll.store in (1,2)
     and p.prod='A'
group by p.prod;

\echo
\echo cat food in stores 1 & 2 (sans products):
select   sum(s1.units) as store_1
       , sum(s2.units) as store_2
       , sum(sAll.units) as store_All
from     sales s1
       , sales s2
       , sales sAll
where    s1.store=1 and s1.prod = 'A'
     and s2.store=2 and s2.prod = 'A'
     and s2.store in (1,2) and sAll.prod = 'A'
;

__END SCRIPT__

When run produces:

animals=# \i animal_food.sql
DROP
CREATE
INSERT 672972905 1
INSERT 672972906 1
DROP
CREATE
INSERT 672972934 1
INSERT 672972935 1

cat food in store 1:
 prod | store_1
------+---------
 A    |      50
(1 row)


cat food in store 2:
 prod | store_2
------+---------
 A    |     100
(1 row)


cat food in stores 1 & 2:
 prod | store_1 | store_2 | store_all
------+---------+---------+-----------
 A    |     100 |     200 |       150
(1 row)


cat food in stores 1 & 2 (sans products):
 store_1 | store_2 | store_all
---------+---------+-----------
     100 |     200 |       150
(1 row)


You can see that store totals for 1 and 2 are not reported correctly
in the last two cases.  I can see how this happens, however one would
expect the engine to keep s1, and s2 figures separate from sAll
figures.

How do I get around this?  Thanks -dl


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com