Select to create balance based on previous month’s value

Question

I have the following situation. I have a Mysql database where one of the columns (liquido_dia) brings the daily value generated (positive or negative), I’m already mounting a demonstrative per month of the value generated in the month using GROUP BY MONTH(data).

What I need now is to generate a new column (image below) with accumulated balance, always taking the value of the previous month and adding to the new month. I am programming in PHP with the database in Mysql

SQL I’m already doing

SELECT * FROM operacoes WHERE id_usuario = '{$codigoUser}' GROUP BY MONTH(data)

Screen I’m already bringing some data, but note that the ACUMUALDO column is coming to sum wrong, is what I am trying to fix in select

This will be used to check if the user is with positive or negative balance in the month and know if he should generate tax DARF or make negative compensation for the next months.

Thank you all

Answer 1

You can use a Mysql variable

Example in SQLFIDDLE: http://sqlfiddle.com/#! 9/5ed061/4

CREATE TABLE `operacoes` (
  `id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `mes` date NOT NULL,
  `valor` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `operacoes` (`id`, `mes`, `valor`) VALUES
(1, '2020-01-01', '670.04'),
(2, '2020-02-01', '453.00'),
(3, '2020-03-01', '154.00'),
(4, '2020-04-01', '543.00')

set @csum := 0;
select mes, valor, (@csum := @csum + valor) as acumulado
from operacoes
order by mes;

This calculates the partial sum in a current form

Answer 2

thank you for your reply. I ran your query in my database to test, see how it returned

Only adjustments to the names of my columns and gave a GORUP BY to bring the sum of each month

set @csum := 0;
select data, liquido_dia, (@csum := @csum + liquido_dia) as acumulado from operacoes GROUP BY month(data);