Re: Screwy behavior with SUM and multiple joins to same

Stephan Szabo
sszabo@bigpanda.com

On Tue, 27 Aug 2002, David Link 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'
> ;
>

In these last two the joins result in two rows.
s1.units is 50 in each row, s2.units is 100 in each
row. When you sum them you get 100 and 200.

If you want the queries to be separate, you probably
want subqueries in the general form
select p.prod, (select sum(s1.units) from store_1 where s1.store=1 and
s1.prod=p.prod), ... from product p where p.prod='A';