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