| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie : Group by price SQL
You can also do it with decode, least and greatest:
select sal
, decode(least(sal, 1000),1000,0,sal) under_1000
, decode(least(sal,
2000),2000,0,decode(greatest(sal,1000),1000,0,sal)) from_1000_to_2000
, decode(least(sal,
3000),3000,0,decode(greatest(sal,2000),2000,0,sal)) from_2000_to_3000
, decode(least(sal, 3000),3000,sal,0) over_3000
from emp
SAL UNDER_1000 FROM_1000_TO_2000 FROM_2000_TO_3000 OVER_3000
---------- ---------- ----------------- ----------------- ----------
800 800 0 0 0
1600 0 1600 0 0
1250 0 1250 0 0
2975 0 0 2975 0
1250 0 1250 0 0
2850 0 0 2850 0
2450 0 0 2450 0
3000 0 0 0 3000
5000 0 0 0 5000
1500 0 1500 0 0
1100 0 1100 0 0
SAL UNDER_1000 FROM_1000_TO_2000 FROM_2000_TO_3000 OVER_3000
---------- ---------- ----------------- ----------------- ----------
950 950 0 0 0
3000 0 0 0 3000
1300 0 1300 0 0
14 rows selected.
I've left out the group by so you can see what it does.
Richard.
> I need to create a daily report that shows management sales group by
> price. I wanted to know if it is possible to use a single SQL
> statement in order to do this. The report will look something like
> this :
>
> Column A Column B Column C Total
> of A, B, C
>
> Price < $10 Price > $11 to < $20 Price > $21
>
> Customer A $1,000.00 $500.00 $20.00
> $1,520.00
-- ------------------------------------------------------------------------------- Remove FRUITBAT for a valid Email address.. Orinda Software make "OrindaBuild", which generates Java JDBC access code for calling PL/SQL procedures. www.orindasoft.comReceived on Wed Jul 30 2003 - 06:41:56 CDT
 |
 |