Re: Screwy behavior with SUM and multiple joins to same

Subject: Re: [GENERAL] Screwy behavior with SUM and multiple joins to same
From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
===
> > 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
> ;

Given the data you gave before, I don't believe this will work any
better. The join and where still give 2 rows out.

The first part
from sales s1 where s1.store=1 and s1.prod='A' returns one row.
The second part
from sales s2 where s2.store=2 and s2.prod='A' returns one row.
The third part
from (select ...) as sAll where s1.prod=sAll.prod returns two
rows.

When you do the join, you end up with two rows out where
the s1 and s2 parts get duplicated.

Maybe something like:
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, sum(units)
from sales s
where s.prod = 'A'
and s.store in (1,2)
group by s.prod) as sAll
where s1.store=1 and s1.prod = 'A'
and s2.store=2 and s2.prod = 'A'
and s1.prod = sAll.prod

That'll make the inner subselect give
one row I think.

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

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