Re: Screwy behavior with SUM and multiple joins to same

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

Sorry, I didn't see this earlier.
Subquery in the SELECT Clause. I suppose. But then I have to repeat a
bunch of logic for each store (the real problem has more than just two
"stores").

I've created a subquery in the FROM Clause working as if it were a TEMP
table. something like this:

select sum(s1.units) as store_1
, sum(s2.units) as store_2
, sum(sAll.units) as store_All
from sales s1
, sales s2
, (select prod, units
from sales s
where s.prod = 'A'
and s.store in (1,2) ) as sAll
where s1.store=1 and s1.prod = 'A'
and s2.store=2 and s2.prod = 'A'
and s1.prod = sAll.prod
;

__________________________________________________
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