subtraction from previous value in mysql

Question

I have a table like

    Id_indicator    Value   trend   Date_data
1   0   0   2011-08-18 09:16:15
1   2   1   2011-08-18 10:16:15
1   1   -1  2011-08-18 11:16:15
1   2   1   2011-08-18 12:16:15
2   21  0   2011-08-18 13:16:15
2   21  0   2011-08-18 14:16:15
2   21  0   2011-08-18 15:16:15
3   3   0   2011-08-18 16:16:15
3   4   1   2011-08-18 17:16:15
3   4   0   2011-08-18 18:16:15
4   4   0   2011-08-18 19:16:15

I need to find out what is the difference between previous values based on id_indicator and add a column in the right and input that value. example as

    Id_indicator    Value   trend   Date_data   Difference 
1   0   0   2011-08-18 09:16:15 0
1   2   1   2011-08-18 10:16:15 2
1   1   -1  2011-08-18 11:16:15 -1
1   2   1   2011-08-18 12:16:15 1
2   21  0   2011-08-18 13:16:15 0
2   21  0   2011-08-18 14:16:15 0
2   21  0   2011-08-18 15:16:15 0
3   3   0   2011-08-18 16:16:15 0
3   4   1   2011-08-18 17:16:15 1
3   4   0   2011-08-18 18:16:15 0
4   4   0   2011-08-18 19:16:15 0

Thanks

Possible duplicate of [MySQL - Subtracting value from previous row, group by](http://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by) — CLAbeel, Nov 17 '16 at 16:12

score 0 · Answer 1 · answered Nov 17 '16 at 15:54

You could first create a lookup which gives you the previous timestamp for each id and date and then join to this lookup.

Something like this (untested):

SELECT * -- t1.value - t2.vaue...
FROM
  (SELECT t1.Id_indicator,
          t1.Date_data,
          max(t2.Date_date) AS prev_date_data
   FROM TABLE t1
   LEFT JOIN TABLE t2 ON t1.Id_indicator = t2.Id_indicator
   AND t2.Date_data < t1.Date_data
   GROUP BY 1, 2) AS lookup
LEFT JOIN TABLE t1 ON t1.Id_indicator = lookup.Id_indicator
AND t1.Date_data = lookup.Date_data
LEFT JOIN TABLE t2 ON t2.Id_indicator = lookup.Id_indicator
AND t2.Date_data = lookup.prev_date_data

subtraction from previous value in mysql

1 Answers1