Balance in real time in a query depending on a column

I admit, I'm going to make a bit of a trap and assume a piece of information that does not appear in the question: the table has a primary key of numeric type. In this way the structure of the table would be (at least) something like this:

• id: primary key of type int .
• name: varchar type (50) with index .
• total: type int , may contain the value null .
• less: type int and can not be null .

And the contents of the table will be as follows:

id     nombre       total      menos
----   ----------   --------   --------
1      Data1        10         2
2      Data1        10         2
3      Data2        50         2
4      Data2        50         10

So what you would like to do is the following in the query:

• For the first field, select the name, without further ado.
• The same for the second field: simply select the total.

• The third field is a little more complicated:

  • On the one hand we need the total (which would be the highest value for that name the total of that column)
  • On the other hand we need the subtraction of everything that has been calculated so far (a SUM of all the previous values for this name)

  once you have those two subqueries, subtract one from the other.

I know it may not be the most efficient way, but you get the result you expect and it's not an overly large query.

The code of the query would look like this:

SELECT a.nombre, 
       a.total, 
       a.menos, 
       a.total - (SELECT SUM(b.menos) FROM 'test' b WHERE b.id <= a.id AND b.nombre = a.nombre) AS resta
FROM   'test' a 
ORDER  BY a.nombre ASC, a.total DESC

When that query is executed, the result obtained is the following:

nombre       total      menos       resta
----------   --------   --------    -----
Data1        10         2           8
Data1        10         2           6
Data2        50         2           48
Data2        50         10          38

name is sim_items_djai_declaacancelar total is sim_items_djai_cantidaddec less is sim_items_djai_decla_cantidaddec

(SELECT
cmn_lst_clientes._desc AS cmn_lst_clientes_desc,
cmn_lst_clientes_un._desc AS cmn_lst_clientes_un_desc,
sim_items_djai_decla._declaacancelar AS sim_items_djai_declaacancelar,
sim_caratula._oficializacion AS sim_caratula_oficializacion,
opr_djai_estado._fb AS opr_djai_estado_fb,
opr_djai_estado._fv AS opr_djai_estado_fv,
sim_items_djai_decla._itemacancelar AS sim_items_djai_decla_itemacancelar,
opr_lst_djai_estados._descesAR as opr_lst_djai_estados_desc,
sim_items_djai._cantidaddec as sim_items_djai_cantidaddec,
sim_lst_um._descesAR as sim_lst_um_desc,
sim_items_djai_decla._destsimn as sim_items_djai_decla_destsimn,
sim_items_djai_decla._oficializacion AS sim_items_djai_decla_oficializacion,
sim_items_djai_decla._item as sim_items_djai_decla_item,
sim_items_djai_decla._cantidaddec as sim_items_djai_decla_cantidaddec,
(sim_items_djai._cantidaddec - sim_items_djai_decla._cantidaddec) as _varSaldo2d
FROM
sim_items AS sim_items_djai_decla USE INDEX (_declaacancelar)
INNER JOIN sim_caratula USE INDEX (PRIMARY) ON sim_items_djai_decla._declaacancelar = sim_caratula._destsimn AND sim_caratula._destsim = 'SIMI'
INNER JOIN opr_djai_estado USE INDEX (_destsimnitem) ON sim_items_djai_decla._declaacancelar = opr_djai_estado._destsimn AND sim_items_djai_decla._itemacancelar = opr_djai_estado._item
INNER JOIN opr_lst_djai_estados USE INDEX (PRIMARY) ON opr_djai_estado._estado = opr_lst_djai_estados._id
LEFT JOIN sim_items AS sim_items_djai USE INDEX (PRIMARY) ON opr_djai_estado._destsimn = sim_items_djai._destsimn AND opr_djai_estado._oficializacion = sim_items_djai._oficializacion AND opr_djai_estado._item = sim_items_djai._item
INNER JOIN sim_lst_um USE INDEX (PRIMARY) ON sim_items_djai._unidaddec = sim_lst_um._id
INNER JOIN opr_djai USE INDEX (PRIMARY) ON opr_djai_estado._destsimn = opr_djai._destsimn AND opr_djai_estado._oficializacion = opr_djai._oficializacion
INNER JOIN cmn_lst_clientes USE INDEX (PRIMARY) ON opr_djai._cliente = cmn_lst_clientes._id
INNER JOIN cmn_lst_clientes_un USE INDEX (PRIMARY) ON opr_djai._cliente = cmn_lst_clientes_un._cliente AND opr_djai._un = cmn_lst_clientes_un._un
WHERE
(sim_caratula._oficializacion >= '2015-12-22'
and sim_caratula._oficializacion <= '2016-04-29')
and opr_djai._cliente = '61'
GROUP BY
sim_items_djai_decla._declaacancelar,
sim_items_djai_decla._itemacancelar,
sim_items_djai_decla._destsimn)
UNION
(SELECT
cmn_lst_clientes._desc AS cmn_lst_clientes_desc,
cmn_lst_clientes_un._desc AS cmn_lst_clientes_un_desc,
sim_items_djai_decla._destsimn AS sim_items_djai_declaacancelar,
sim_items_djai_decla._oficializacion AS sim_caratula_oficializacion,
opr_djai_estado._fb AS opr_djai_estado_fb,
opr_djai_estado._fv AS opr_djai_estado_fv,
sim_items_djai_decla._item AS sim_items_djai_decla_itemacancelar,
opr_lst_djai_estados._descesAR as opr_lst_djai_estados_desc,
sim_items_djai_decla._cantidaddec as sim_items_djai_cantidaddec,
sim_lst_um._descesAR as sim_lst_um_desc,
'' as sim_items_djai_decla_destsimn,
null AS sim_items_djai_decla_oficializacion,
'' as sim_items_djai_decla_item,
0 as sim_items_djai_decla_cantidaddec,
sim_items_djai_decla._cantidaddec as _varSaldo2d
FROM
sim_items AS sim_items_djai_decla USE INDEX (PRIMARY)
LEFT JOIN sim_caratula USE INDEX (PRIMARY) ON sim_items_djai_decla._declaacancelar = sim_caratula._destsimn AND sim_caratula._destsim = 'SIMI'
LEFT JOIN sim_items ON sim_items_djai_decla._destsimn = sim_items._declaacancelar AND sim_items_djai_decla._item = sim_items._itemacancelar
LEFT JOIN opr_djai ON sim_items_djai_decla._destsimn = opr_djai._destsimn AND sim_items_djai_decla._oficializacion = opr_djai._oficializacion
LEFT JOIN cmn_lst_clientes ON opr_djai._cliente = cmn_lst_clientes._id
LEFT JOIN cmn_lst_clientes_un ON opr_djai._cliente = cmn_lst_clientes_un._cliente AND opr_djai._un = cmn_lst_clientes_un._un
LEFT JOIN opr_djai_estado ON sim_items_djai_decla._destsimn = opr_djai_estado._destsimn AND sim_items_djai_decla._oficializacion = opr_djai_estado._oficializacion AND sim_items_djai_decla._item = opr_djai_estado._item
LEFT JOIN opr_lst_djai_estados ON opr_djai_estado._estado = opr_lst_djai_estados._id
LEFT JOIN sim_lst_um ON sim_items_djai_decla._unidaddec = sim_lst_um._id
WHERE
ISNULL(sim_items._declaacancelar)
AND sim_items_djai_decla._oficializacion >= '2015-12-22'
AND sim_items_djai_decla._oficializacion <= '2016-04-29'
and opr_djai._cliente = '61'
GROUP BY
sim_items_djai_decla._destsimn,
sim_items_djai_decla._oficializacion,
sim_items_djai_decla._item)
ORDER BY
sim_items_djai_declaacancelar,
sim_caratula_oficializacion,
sim_items_djai_decla_itemacancelar

Good ... I guess I should have an id or some increment that tells you what the registration order is but you would not know which one comes first, assuming this: It should look like this:

id A B C
1 data1 10 2
2 data1 2
3 data2 50 2
4 data2 10
5 data1 6
6 data2 7  so the query if it was a table. would be the following: ** SELECT @ data1: = 0;

SELECT *, IF (ISNULL (B), @ data1: = (@ data1-C), @ data1: = (B-C)) AS D

FROM subtraction as res where A = 'data1'; ** I did it for data1, it depends on you how you manage the rest, @ data1, 'data1' in your query you can make it modifiable and return it for each name, or with a select group by or a distinct first load the values that will enter there , or the prepare, there are different ways that can help you, the other simpler would be a stored procedure. and I would return the following : id A B C D
1 data1 10 2 8 2 data1 2 6 5 data1 6 0 11 data1 10 -10

The only problem is that if there are more subtractions you will return negative values.

I hope it serves you.

You could use a TRIGGER, which is executed when inserting or updating a record in the table, I leave you an example of code that could represent the scenario you comment, which saves the result of the subtraction in the column 'calculated 'previously created.

DELIMITER |

CREATE TRIGGER calculate_column_insert BEFORE INSERT ON my_table
  FOR EACH ROW BEGIN
    SET NEW.calculated = NEW.a - NEW.b;
  END;
|

CREATE TRIGGER calculate_column_update BEFORE UPDATE ON my_table
  FOR EACH ROW BEGIN
    SET NEW.calculated = NEW.a - NEW.b;
  END;
|

DELIMITER ;

If you want to perform the calculation from the SELECT, you could create an alias for the column calculated as follows.

SELECT A, B, (A + B) AS C FROM MyTable;