0

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.

2

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.