Calculate balance in view mysql

Question

I have a view in mysql, working well and presents the results correctly.

   SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
   (select sum(debe) - sum(haber)
    from cuotas m2
    where m2.id_cuota <= m.id_cuota
   ) as balance
   FROM `cuotas` m
   ORDER BY m.`id_cuota` DESC;


  id_cuota  clv_cuota  debe    haber     balance
  22          115      25.00    0.00     125.00
  21          115       0.00  150.00     100.00
  20          115      50.00    0.00     250.00
  19          116     100.00    0.00     200.00
  18          115     100.00    0.00     100.00

I would like to know how to make the query but the result is for example. "clv_cuotas=115". thus being the result.

  id_cuota  clv_cuota  debe    haber     balance
  22          115      25.00    0.00      25.00
  21          115       0.00  150.00       0.00
  20          115      50.00    0.00     150.00
  18          115     100.00    0.00     100.00

Answer 1

Without redoing your query itself, the issue is that the running total balance REQUIRES the 116 to get the proper balance, only AFTER the values are computed, you can remove them. So, wrap in a query such as:

select PQ.*
   from  ( your existing query )  PQ
   where PQ.clv_cuto = 115

This way the internal rows are computed based on all clv_cuto values and only excluded AFTER-the-fact.

Note: This should show the proper balances you expect from the first query, just returning only those 115 entries.

To answer/clarify your needs, since you only care about the net activity for account 115, just put that in as your where clause. It needs to be applied to both the inner AND outer part of the query.

SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
   (select sum(debe) - sum(haber)
    from cuotas m2
    where m2.id_cuota <= m.id_cuota
      AND m2.clv_cuota = 115
   ) as balance
   FROM `cuotas` m
   WHERE m2.clv_cuota = 115
   ORDER BY m.`id_cuota` DESC;