Re: Screwy behavior with SUM and multiple joins to same table

Incidently, this behavior is exactly the same in MySQL and M$
SQLServer.

Best I could do to resolve it is to create a complex sub-select in the
FROM clause repeating much of the main SQL statement.

I could also denormalize the table by having store=100 records for pre
calculated sums.

But still it strikes me as funny that this is the correct behavior.  Or
is it a curious side effect?


--- David Link <dvlink@yahoo.com> wrote:
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


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