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

I think you have a conceptual problem here. Was happens is that the database
prforms your joins first to produce a virtual table of 8 rows, it then
applies your where lauses to chop that down and finally does the sum().

Of course, that is only the conceptual model. It's actually more intelligent
than that but it gives you an idea of why you're are getting the results you
are.

Subqueries may be what you're looking for. This is how SQL works and any
database will do the same.

Hope this helps,

On Tue, Aug 27, 2002 at 12:36:42PM -0700, 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'
> ;
>
> __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

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.