Ed0906 Ed0906 - 7 months ago 10
SQL Question

MySql: Building this query without the subselect

I have a transaction log table in the form:

| id | date | type | symbol | volume | unit_price | user_id |
| 1 | 2016-01-01 | BUY | AAPL | 100 | 100.00 | a |
| 2 | 2016-01-02 | SELL | AAPL | 50 | 110 | a |


I am trying to build a view that shows the current balance based on these transactions. Something in the form:

| user_id | symbol | total_volume | value |


So far I have:

SELECT
t.user_id,
t.symbol,
sum(t.volume * t.multiplier) as total_volume,
sum(t.volume * t.unit_price * t.multiplier) as value
FROM (
SELECT
*,
CASE type
WHEN 'SELL' THEN -1
WHEN 'BUY' THEN 1
END as multiplier
FROM transaction
) t
GROUP BY t.user_id, t.symbol;


This gets me what I need but I can't create this as a view because of the sub select. Is there an alternate way I could do this which would enable me to create it as a view?

Answer

Just use the condition in the sum:

SELECT t.user_id, t.symbol,
       sum(case when type = 'SELL' then - volume
                     when type = 'BUY' then volume
            end)
          ) as total_volume,
       sum(t.volume * t.unit_price *
           (case when type = 'SELL' then -1
                 when type = 'BUY' then 1
            end))  as value
FROM transaction t
GROUP BY t.user_id, t.symbol;

If type only takes on the two values, you can simplify this to:

SELECT t.user_id, t.symbol,
       sum(case when type = 'SELL' then -t.volume else t.volume end)  as total_volume,
       sum(t.volume * t.unit_price *
           (case when type = 'SELL' then -1 else 1 end)
            end))  as value
FROM transaction t
GROUP BY t.user_id, t.symbol;