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

Subject: Re: [GENERAL] Screwy behavior with SUM and multiple joins to same table
From: David Link <dvlink(at)yahoo(dot)com>
===
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(at)yahoo(dot)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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html