How to sum the buy order and sell order in one row per each ticker

Question

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker.

I have one table containing the following: iOrder 1 = Buy, -1 = Sell.

Ticker | iOrder | Price | Amount
1,1,90,180
1,1,89.5,179
1,-1,91,91
2,1,5,20
2,-1, 4,16
2,-1,3,9

My Expected resulting Table:

Ticker | BuyAmount |  SellAmount
1, 359, 91
2,20, 25

Ticker 1 Buy amount 359 =two Buy order with amount equals 180 and 179 , therefore sum of the buy order for ticker 1 = 180+179 = 359

I just know how to do one table containing Ticker and BuyAmount.

 select Ticker, sum(Amount) as BuyAmount 
    from AAMMNews where iOrder = 1 group by Ticker

Using union or join? Please help, experts. Thanks.

Answer 1

below should help you :

CREATE TABLE dbo.Trading
        ([Ticker] int, [iOrder] int, [Price] int, [Amount] int)
    ;

INSERT INTO dbo.Trading
    ([Ticker], [iOrder], [Price], [Amount])
VALUES
    (1, 1, 90, 180),
    (1, 1, 89.5, 179),
    (1, -1, 91, 91),
    (2, 1, 5, 20),
    (2, -1, 4, 16),
    (2, -1, 3, 9)
;


SELECT Ticker
    ,sum(CASE 
            WHEN iorder = 1
                THEN Amount
            ELSE 0
            END) AS BuyAmount
    ,sum(CASE 
            WHEN iorder = - 1
                THEN amount
            ELSE 0
            END) AS SellAmount
FROM dbo.Trading
GROUP BY Ticker