2
Good afternoon, I’m having a problem to get a relatively common result, I’ve seen some materials talking about but none applied to my specific need.
I need the cumulative total of each release, adding up all the previous ones, taking into account not only the launch sequence but also the release date. Note in the following examples that the last release should be the first to be calculated.
Information I have
Id| Descricao | Valor | Data
1 | PAGAMENTO ENERGIA |- 100.00 | 2021-01-11
2 | VENDAS DO DIA |1500.00 | 2021-01-11
3 | SALDO INICIAL |1000.00 | 2021-01-10
Result I get with the command:
SELECT cm.id,
cm.descricao,
cm.valor,
cm.datapagamento,
Sum(CASE
WHEN cm2.tipo = 1 THEN ( cm2.valor * -1 )
ELSE cm2.valor
END) AS Total
FROM conta_movimentacao AS cm
INNER JOIN conta_movimentacao AS cm2
ON cm.id >= cm2.id
GROUP BY cm.id;
Id| Descricao | Valor | Data | Total
1 | PAGAMENTO ENERGIA |-100.00 | 2021-01-11 | -100.00
2 | VENDAS DO DIA |1500.00 | 2021-01-11 | 1400.00
3 | SALDO INICIAL |1000.00 | 2021-01-10 | 2400.00
What I really need:
Id| Descricao | Valor | Data | Total
3 | SALDO INICIAL |1000.00 | 2021-01-10 | 1000.00
1 | PAGAMENTO ENERGIA |-100.00 | 2021-01-11 | 900.00
2 | VENDAS DO DIA |1500.00 | 2021-01-11 | 2400.00
Someone could help me?
A
ORDER BY cm.DataPagamentodoes not answer?– anonimo
Worse than no friend, I have tried several ways. (I put the 3 tables organized in the question but for some reason it was not...)
– Josemir Moura
"Running Total" with window functions https://popsql.com/learn-sql/mysql/how-to-calculatumulative-sum-running-total-in-mysql
– Motta
It is an accounting system, this modeling is not appropriate. You would at least have to have one table identifying the accounts and another with the entries using the double start method so then follow the cash flow and assemble periodic balances.
– Augusto Vasques
@Motta I managed to settle with your friend tip! , Thank you.
– Josemir Moura