How to calculate the cumulative sum/subtraction of previous records in SQL?

Question

I’m trying, unsuccessfully, to create a column SALDO in order to keep track of the Cash Flow balance. I imagined doing this in SQL itself, generating a column that would calculate the balance.

For this, I need to have access to the previous record, to be able to do the operation of sum or discount, based on the type of movement (tipo).

The structure is only one table, with the field valor and with the countryside tipo, which may be E (input, sum) or S (output, subtract).

SELECT lct.id, lct.descricao, 
       DATE_FORMAT (lct.data,'%d/%m/%Y') as "data", 
       lct.tipo, 
       CONCAT('R$ ', FORMAT(lct.valor,2,'de_DE')) as "valor", 
       emp.descricao as "empreendimento", 
       cat.descricao as "categoria",
       con.descricao as "conta",
       cax.descricao as "caixa"
       FROM empreendimentos emp, contas con, 
       lancamentos lct, concategorias cat, caixas cax
       WHERE lct.idconta = con.id 
       AND lct.idempreendimento = emp.id 
       AND lct.idcategoria = cat.id
       AND lct.idcaixa = cax.id
       AND cax.id = 1
       AND lct.flagstatus = 1
       ORDER BY lct.data ASC, lct.id

Given the above code, it would be possible to accomplish what I need?

Answer 1

You can use the function SUM for this, combining with the CASE WHEN for:

• CASE type_movement = 'E' sum "value"
• ELSE (accounting case) sum "- value", that is, subtract when it is "S"

Here an example of this part, I tried to use names similar to those in your table:

SELECT lct.id_conta,
       SUM(CASE WHEN lct.tipo_movimento = 'E' 
                THEN lct.valor 
                ELSE - lct.valor 
           END) AS saldo
FROM lancamentos lct
GROUP BY lct.id_conta

Simply add the other fields and tables of your query. Note that, as the balance should be calculated per account, I added "id_account" to GROUP BY, otherwise will mix the accounts by miscalculating the balance.

You can see it working here: http://sqlfiddle.com/#! 9/6c37dd/1

Answer 2

I was able to solve using the following:

    SELECT sld.data,
       sld.tipo,
       CONCAT('R$ ', FORMAT(sld.valor,2,'de_DE')) as "valor",       
       CASE
          WHEN sld.tipo = 'S'
          THEN
            CONCAT('R$ ', FORMAT(@running_total:=@running_total - sld.valor,2,'de_DE'))
          ELSE
            CONCAT('R$ ', FORMAT(@running_total:=@running_total + sld.valor,2,'de_DE')) 
      END  
      AS cumulative_sum     
FROM
(SELECT lct.id, lct.descricao, 
  lct.data as "data", 
  lct.tipo as "tipo", 
  lct.valor as "valor",   
  emp.descricao as "empreendimento", 
  cat.descricao as "categoria",
  con.descricao as "conta",
  cax.descricao as "caixa"
  FROM empreendimentos emp, contas con, 
  lancamentos lct, concategorias cat, caixas cax
  WHERE lct.idconta = con.id 
  AND lct.idempreendimento = emp.id 
  AND lct.idcategoria = cat.id
  AND lct.idcaixa = cax.id
  AND cax.id = 1
  AND lct.flagstatus = 1
  GROUP BY lct.id
  ORDER BY lct.data) sld
JOIN (SELECT @running_total:=0) r
ORDER BY sld.data;

Using variables, I was able to achieve the expected result:

(data, tipo, valor, cumulative_sum) VALUES
('02/01/2021 00:00:00', 'E', 'R$ 253.571,83', 'R$ 253.571,83'), 
('02/01/2021 00:00:00', 'S', 'R$ 200,00', 'R$ 253.371,83'), 
('04/01/2021 00:00:00', 'S', 'R$ 930,05', 'R$ 252.441,78'), 
('04/01/2021 00:00:00', 'S', 'R$ 2.317,38', 'R$ 250.124,40'), 
('05/01/2021 00:00:00', 'E', 'R$ 3.000,00', 'R$ 253.124,40'), 
('05/01/2021 00:00:00', 'S', 'R$ 25,80', 'R$ 253.098,60'), 
('11/01/2021 00:00:00', 'E', 'R$ 3.000,00', 'R$ 256.098,60'), 
('15/01/2021 00:00:00', 'S', 'R$ 2.000,00', 'R$ 254.098,60'), 
('22/01/2021 00:00:00', 'S', 'R$ 199,99', 'R$ 253.898,61'), 
('22/01/2021 00:00:00', 'E', 'R$ 2.000,00', 'R$ 255.898,61'), 
('29/01/2021 00:00:00', 'S', 'R$ 213,58', 'R$ 255.685,03'), 
('03/02/2021 00:00:00', 'S', 'R$ 979,00', 'R$ 254.706,03'), 
('03/02/2021 00:00:00', 'S', 'R$ 27,70', 'R$ 254.678,33'), 
('05/02/2021 00:00:00', 'S', 'R$ 206,00', 'R$ 254.472,33'), 
('17/02/2021 00:00:00', 'S', 'R$ 1.841,00', 'R$ 252.631,33'), 
('03/03/2021 00:00:00', 'S', 'R$ 25,80', 'R$ 252.605,53'), 
('03/03/2021 00:00:00', 'S', 'R$ 979,00', 'R$ 251.626,53'), 
('05/03/2021 00:00:00', 'S', 'R$ 206,38', 'R$ 251.420,15'), 
('09/03/2021 00:00:00', 'E', 'R$ 3.000,00', 'R$ 254.420,15'), 
('31/03/2021 00:00:00', 'S', 'R$ 1.356,00', 'R$ 253.064,15'), 
('01/04/2021 00:00:00', 'E', 'R$ 2.000,00', 'R$ 255.064,15'), 
('05/04/2021 00:00:00', 'S', 'R$ 25,80', 'R$ 255.038,35'), 
('05/04/2021 00:00:00', 'S', 'R$ 979,00', 'R$ 254.059,35'), 
('08/04/2021 00:00:00', 'E', 'R$ 3.000,00', 'R$ 257.059,35'), 
('09/04/2021 00:00:00', 'E', 'R$ 2.000,00', 'R$ 259.059,35'), 
('09/04/2021 00:00:00', 'S', 'R$ 209,00', 'R$ 258.850,35'), 
('27/04/2021 00:00:00', 'S', 'R$ 294,34', 'R$ 258.556,01'), 
('27/04/2021 00:00:00', 'S', 'R$ 10.500,00', 'R$ 248.056,01'), 
('28/04/2021 00:00:00', 'S', 'R$ 42,09', 'R$ 248.013,92')

Source of the idea: https://popsql.com/learn-sql/mysql/how-to-calculate-cumulative-sum-running-total-in-mysql