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@megazone23.bigpanda.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