Re: Screwy behavior with SUM and multiple joins to same

> > \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