Re: Screwy behavior with SUM and multiple joins to same

--- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> 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.
>

Yes, you are absolutely right:  with the SUM() in the subselect to get
back only one row and that's what I have.

c/
  , (select prod, sum(units)
/
  , (select prod, sum(units) as units



__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com