mysql window function with case

Question

I'm trying to perform a window function with a built in case. Here's an example which should make it more clear.

Original Table:

SELECT
    trade_date,
    ticker,
    trans_type,
    quantity
FROM
    orders
WHERE
    trade_date >= '2020-11-16';

Results:

|trade_date|ticker|trans_type|quantity|
|:---------|:-----|:---------|-------:|
|2020-12-10|FB    |BUY       |100     |
|2020-12-28|FB    |BUY       |50      |
|2020-12-29|FB    |SELL      |80      |
|2020-12-30|FB    |SELL      |30      |
|2020-12-31|FB    |BUY       |40      |
|2020-11-16|AAPL  |BUY       |30      |
|2020-11-17|AAPL  |SELL      |70      |
|2020-11-20|AAPL  |BUY       |50      |
|2020-11-24|AAPL  |BUY       |40      |

What I'd like to see is the following:

|trade_date|ticker|trans_type|quantity|net_shares|
|:---------|:-----|:---------|-------:|---------:|
|2020-12-10|FB    |BUY       |100     |100       |
|2020-12-28|FB    |BUY       |50      |150       |
|2020-12-29|FB    |SELL      |80      |70        |
|2020-12-30|FB    |SELL      |30      |40        |
|2020-12-31|FB    |BUY       |40      |80        |
|2020-11-16|AAPL  |BUY       |30      |30        |
|2020-11-17|AAPL  |SELL      |70      |-40       |
|2020-11-20|AAPL  |BUY       |50      |10        |
|2020-11-24|AAPL  |BUY       |40      |50        |

This is the query I'm trying:

SELECT
    trade_date,
    ticker,
    trans_type,
    quantity,
    SUM(CASE WHEN trans_type='SELL' THEN -quantity ELSE quantity END) OVER (PARTITION BY ticker ORDER BY trade_date ASC) AS net_quantity
FROM
    orders
WHERE
    trade_date >= '2020-11-16';

but it's giving incorrect results.

I'm happy to provide additional information if needed.

Thanks!

question from:https://stackoverflow.com/questions/65830746/mysql-window-function-with-case

Answer 1

Waitting for answers