MYSQL query group by and cumulative sum for financial orderbook

Question

Hello i have the following dataset

id price amount
1    10    1
2    20    2
3    20    1.5
4    21    1
5    21    2

SELECT amount, price, (@CumulativeSum := @CumulativeSum + amount) AS CumSum 
FROM orderbook 

And it is working fine. I would like to populate my python dictionary using also a "GROUP BY price" clause but this is affecting my final result. The final data should show the SUM of the amounts grouped by price.

I tried the following queries

SELECT amount, price, (@CumulativeSum := @CumulativeSum + amount) AS CumSum 
FROM orderbook  
GROUP BY price

SELECT SUM(amount), price, (@CumulativeSum := @CumulativeSum + amount) AS CumSum 
FROM orderbook  
GROUP BY price

SELECT amount, price, (@CumulativeSum := @CumulativeSum + SUM(amount)) AS CumSum 
FROM orderbook 

But the cumulative sum or the grouped sums are always wrong.

The final result should be a simple order book for a financial market.

The desired output is

price amount CumSum
10    1     1
20    3.5   4.5
21    3     7.5

Thanks for the hints

question from:https://stackoverflow.com/questions/65925372/mysql-query-group-by-and-cumulative-sum-for-financial-orderbook

Answer 1

I use mysql 5.7 (since this version is using by me). you want to sum cummulative from the result of amount on each price, so you should use SUBQUERY in order to aggregation

Try this:

    set @CumulativeSum := 0;
    SELECT price, summ,
   (@CumulativeSum:= @CumulativeSum + summ) as cumsum 
      FROM (SELECT SUM(amount) as Summ
             FROM (SELECT * FROM orderbook) a
             GROUP BY price
             ORDER BY price) b

result

+-------+------+--------+
| price | summ | cumsum |
+-------+------+--------+
|    10 | 1.00 | 1.00   |
|    20 | 3.50 | 4.50   |
|    21 | 3.00 | 7.50   |
+-------+------+--------+

this is the fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4f607e38a23f069829dfaa177a8bc3d3