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

Screwy behavior with SUM and multiple joins to same table

From
David Link
Date:
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

Re: Screwy behavior with SUM and multiple joins to same

From
Oliver Elphick
Date:
On Tue, 2002-08-27 at 18:20, David Link wrote:
> Screwy behavior with SUM and multiple joins to same table:

The screwiness is in your joins:

junk=# select p.prod
junk-#  , s1.units as store_1
junk-#  , s2.units as store_2
junk-#  , sAll.units as store_All
junk-# from product p
junk-#  , sales s1
junk-#  , sales s2
junk-#  , sales sAll
junk-# where p.prod = s1.prod and s1.store=1
junk-#  and p.prod = s2.prod and s2.store=2
junk-#  and p.prod = sAll.prod and sAll.store in (1,2)
junk-#  and p.prod='A' ;
 prod | store_1 | store_2 | store_all
------+---------+---------+-----------
 A    |      50 |     100 |        50
 A    |      50 |     100 |       100
(2 rows)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If any of you lack wisdom, let him ask of God, who
      gives to all men generously and without reproach, and
      it will be given to him."           James 1:5


Re: Screwy behavior with SUM and multiple joins to same

From
Oliver Elphick
Date:
On Wed, 2002-09-04 at 11:23, Oliver Elphick wrote:
> On Tue, 2002-08-27 at 18:20, David Link wrote:
> > Screwy behavior with SUM and multiple joins to same table:
>
> The screwiness is in your joins:

Here's an alternative approach, that is a lot more readable:

SELECT  prod, units AS store_1, 0 AS store_2, units AS store_3
  WHERE store = 1
UNION
SELECT  prod, 0 AS store_1, units AS store_2, units AS store_3
  WHERE store = 2

but it will become unmanageable if there are a lot of stores.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If any of you lack wisdom, let him ask of God, who
      gives to all men generously and without reproach, and
      it will be given to him."           James 1:5