Screwy behavior with SUM and multiple joins to same table
От | David Link |
---|---|
Тема | Screwy behavior with SUM and multiple joins to same table |
Дата | |
Msg-id | 20020827193642.31624.qmail@web13506.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: Screwy behavior with SUM and multiple joins to same
Re: Screwy behavior with SUM and multiple joins to same table Re: Screwy behavior with SUM and multiple joins to same table |
Список | 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
В списке pgsql-general по дате отправления:
Следующее
От: Stephan SzaboДата:
Сообщение: Re: Screwy behavior with SUM and multiple joins to same