Thanks - this helps
Alvin - also thx
"John Gilson" <jag_at_acm.org> wrote in message news:<P6iv9.54069$gB.12635544_at_twister.nyc.rr.com>...
> "NoSpamPlease" <googleposer_at_yahoo.com> wrote in message
> news:499d4ffb.0210281321.bf0918f_at_posting.google.com...
> > 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
>
> CREATE TABLE Trades
> (
> id INT NOT NULL PRIMARY KEY,
> type INT NOT NULL,
> sell DECIMAL(6, 3) NOT NULL,
> buy DECIMAL(6, 3) NOT NULL
> )
>
> SELECT type,
> COUNT(id) AS NumItems,
> SUM(CASE WHEN buy < sell
> THEN 1
> ELSE 0 END) AS NumProfit,
> SUM(CASE WHEN buy > sell
> THEN 1
> ELSE 0 END) AS NumLoss,
> SUM(CASE WHEN buy = sell
> THEN 1
> ELSE 0 END) AS NumEq
> FROM Trades
> GROUP BY type
> ORDER BY type
>
> Regards,
> jag
Received on Wed Oct 30 2002 - 22:48:42 CST
