Re: Help needed with a simple SQL query

From: John Gilson <jag_at_acm.org>
Date: Mon, 28 Oct 2002 21:37:51 GMT
Message-ID: <P6iv9.54069$gB.12635544@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 Mon Oct 28 2002 - 15:37:51 CST