How to find cumulative sum between two dates in MySQL?

How to find cumulative sum between two dates taking into account the previous state?

Putting WHERE condition

WHERE date BETWEEN '2021-02-19 12:00:00'AND '2021-02-21 12:00:00';

doesn’t do the job because the sum starts from the first condition’s date, and not from the first record. I would like to select only part of the whole query (between two dates), but to calculate cumulative sum from the first (initial) state.

I prepared Fiddle

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `date` datetime NOT NULL DEFAULT current_timestamp(),
  `payment` double NOT NULL
);

INSERT INTO `table1` (`id`, `date`, `payment`) VALUES
(1, '2021-02-16 12:00:00', 100),
(2, '2021-02-17 12:00:00', 200),
(3, '2021-02-18 12:00:00', 300),
(4, '2021-02-19 12:00:00', 400),
(5, '2021-02-20 12:00:00', 500),
(6, '2021-02-21 12:00:00', 600),
(7, '2021-02-22 12:00:00', 700);
 version();

SELECT DATE_FORMAT(date, "%Y-%m-%d") AS date, 
payment, SUM(payment) OVER(ORDER BY id) AS balance 
FROM table1
WHERE date BETWEEN '2021-02-19 12:00:00'AND '2021-02-21 12:00:00';

Answer

You must filter the table after you get the cumulative sums:

SELECT *
FROM (
  SELECT DATE(date) AS date, 
         payment, 
         SUM(payment) OVER(ORDER BY id) AS balance 
  FROM table1
) t 
WHERE date BETWEEN '2021-02-19'AND '2021-02-21';

or:

SELECT *
FROM (
  SELECT DATE(date) AS date, 
         payment, 
         SUM(payment) OVER(ORDER BY id) AS balance 
  FROM table1
  WHERE DATE(date) <= '2021-02-21'
) t 
WHERE date >= '2021-02-19';

See the demo.
Results:

date	payment	balance
2021-02-19	400	1000
2021-02-20	500	1500
2021-02-21	600	2100

Answer

Leave a Reply Cancel reply