By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.7.39 |
CREATE TABLE orderbook (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
price INT(100) NOT NULL,
amount decimal(10,2) NOT NULL
);
INSERT INTO orderbook (price, amount)
VALUES
( 10, 1),
(20, 2),
(20, 1.5),
(21, 1),
(21, 2);
Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM orderbook
| id | price | amount |
|---|---|---|
| 1 | 10 | 1.00 |
| 2 | 20 | 2.00 |
| 3 | 20 | 1.50 |
| 4 | 21 | 1.00 |
| 5 | 21 | 2.00 |
set @CumulativeSum := 0;
SELECT price, summ, (@CumulativeSum:= @CumulativeSum + summ) as cumsum FROM (SELECT price, SUM(amount) as Summ
FROM (SELECT * FROM orderbook) a
GROUP BY price
ORDER BY price) b
| price | summ | cumsum |
|---|---|---|
| 10 | 1.00 | 1.00 |
| 20 | 3.50 | 4.50 |
| 21 | 3.00 | 7.50 |