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

Leave a Reply

Your email address will not be published. Required fields are marked *