Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help needed with a simple SQL query
NoSpamPlease wrote:
> Need a SQL Query to generate the following o/p for the i/p
> I/p :
> Id Type Sell Buy
> 1 1 1.669 1.2
> 2 1 3.54 2.759
> 3 2 3.100 4.599
> 4 1 1.100 1.100
> 5 2 3.100 1.600
> 6 3 3.100 1.600
> 7 1 2.109 8.599
> 8 3 1.100 0.599
> 9 3 7.099 9.599
> 10 1 1.54 0.760
>
> Output
> Type NumItems NumProfit NumLoss NumEq
> 1 5 3 1 1
> 2 2 1 1 0
> 3 32 1 0
You need to ORDER BY Type. You need to GROUP BY Type. NumItems is the
COUNT of Id. NumProfit, NumLoss, and NumEq require that you use the
SIGN() function, which returns (-1,0,1) after subtracting Buy from Sell.
To get the counts, you need to DECODE() the return from SIGN() into a
zero or one, which you pass to COUNT(). Look up all these functions in
your class notes (you did listen to your instructor, right).
Received on Mon Oct 28 2002 - 15:44:38 CST