Re: sql question
From: "Obnoxio The Clown" <obnoxio@hotmail.com>
>
>From: "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de>
>>
>>Sorry Clown, don't work!
>
>I don't?
Here's a conceptually similar sample using the stores7 database:
select order_num, sum(quantity) hrosum
from items
where manu_code = "HRO"
group by 1
into temp t1;
select order_num, sum(quantity) anzsum
from items
where manu_code = "ANZ"
group by 1
into temp t2;
select items.order_num, hrosum, anzsum
from items, outer t1, outer t2
where items.order_num = t1.order_num
and items.order_num = t2.order_num
Or if you wanted to be really flash:
select order_num, sum(quantity) hrosum
from items
where manu_code = "HRO"
group by 1
into temp t1;
select order_num, sum(quantity) anzsum
from items
where manu_code = "ANZ"
group by 1
into temp t2;
select items.order_num, nvl(hrosum, 0), nvl(anzsum, 0)
from items, outer t1, outer t2
where items.order_num = t1.order_num
and items.order_num = t2.order_num
:-)
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com