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

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
``````

• Which DBMS are you using? Postgres? Oracle? Aug 15 '14 at 17:33
• I am using mySQL Aug 15 '14 at 18:43

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

([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