How to get sum for each day day of period?

Question

MySQL

There is data on transactions: income-expense. Table: id-date-sum-type (receipt/expenditure). I consider the current balance. All OK. Task: you need to plot the balance sheet for the period. Let's say from January 1 to December 31, broken down by days. I don't understand how to do it right. Make 365 balance calculation queries for each day of the type SELECT SUM(income)-SUM(expense) where the date is from 01.01 to 02.01, then from 01.01 to 03.01, and so on? Or nevertheless SQL is able to make such sampling?

Answer 1

All this is completely suboptimal, since it requires you to count each time from the very beginning, but window functions solve your problem. Requires MySQL 8 (It also works in MariaDB 10.5, I haven't tested it before):

CREATE TABLE entries(
  tran_date TIMESTAMP NOT NULL,
  amount INT NOT NULL
);

INSERT INTO entries VALUES ('2022-01-01 00:01:00', 100),
('2022-01-01 01:01:00', 3400),('2022-01-01 02:01:00', -500),
('2022-01-02 00:01:00', 8000),('2022-01-02 01:01:00', -3900),
('2022-01-02 02:01:00', -900);

WITH day_amounts AS (
  SELECT DATE(tran_date) AS day, SUM(amount) AS amount 
  FROM entries 
  GROUP BY DATE(tran_date)
)
SELECT day, SUM(amount) OVER (ORDER BY day) AS balance 
FROM day_amounts;

https://sqlize.online/sql/mariadb/20805610c3da00c2...