How to calculate the difference between values that are in the same column?

Asked

Viewed 5,263 times

5

I would like to know if there is any way to calculate the difference between values that are in the same column. For example: I have a table with 2 fields: date and balance. I need to add a new field, containing the balance difference from one date to another.

Dia        Saldo      Variação
------------------------------
01/12 ---  10 ------- 0  
02/12 ---  30 ------- 20  
03/12 ---  70 ------- 40  
04/12 ---  80 ------- 10
mysql select-sql

2 answers

4


You can do it this way:

SELECT 
    m.id,
    m.data,
    m.saldo,
    IFNULL(saldo - (SELECT mt.saldo FROM movimento mt WHERE mt.id < m.id ORDER BY mt.id DESC LIMIT 0,1), 0) AS variacao
FROM movimento m

Take a look at the sqlfiddle.com

  • 1

    You can even use the date field itself for comparison and sorting instead of Id (which does not appear in the example and may not even be sequential). Good solution. + 1

  • Sure, as long as there’s only one movement per date

  • In this case it is only one record per date. Thank you very much! I just didn’t understand how to solve by just comparing dates, without using id.

  • 1

    @samuelgp Exactly the same way - in command just switch field id across the countryside dia.

-1

I know two options, you bring this in a consultation as in I calculate between fields

SELECT ValorBoleto + TotalJuros AS Preco FROM fotoprodutos

or this Formula in SQL (English)

SELECT name, black, yellow, white, qty_job (SUM(black) + SUM(yellow) + SUM(white)*0.4) / qty_job AS total FROM counter GROUP BY name;

Another option is to do this via programming language. When you are saving, you calculate the field before filling it out. But in this case, we would need to know the programming language to help you better.

  • 1

    In this specific case a "line-to-line" calculation is required, not "field-to-field".

  • Fields are said to be all lines of the field, although the calculation is done line by line.

  • Then you could post an example here at http://sqlfiddle.com/ ?

Browser other questions tagged mysql select-sql

You are not signed in. Login or sign up in order to post.