MySQL - Subtracting value from previous row, group by

Question

I need to have the consumption value base on previous one by SN number. This is my data:

TABLE EnergyLog

SN     Date                 Value 2380   2012-10-30 00:15:51  21.01 2380   2012-10-31 00:31:03  22.04 2380   2012-11-01 00:16:02  22.65 2380   2012-11-02 00:15:32  23.11 20100  2012-10-30 00:15:38  35.21 20100  2012-10-31 00:15:48  37.07 20100  2012-11-01 00:15:49  38.17 20100  2012-11-02 00:15:19  38.97 20103  2012-10-30 10:27:34  57.98 20103  2012-10-31 12:24:42  60.83 

This is the result I need:

SN      Date                 Value  consumption 2380    2012-10-30 00:15:51  21.01  0 2380    2012-10-31 00:31:03  22.04  1.03 2380    2012-11-01 00:16:02  22.65  0.61 2380    2012-11-02 00:15:32  23.11  0.46 20100   2012-10-30 00:15:38  35.21  0 20100   2012-10-31 00:15:48  37.07  1.86 20100   2012-11-01 00:15:49  38.17  1.1 20100   2012-11-02 00:15:19  38.97  0.8 20103   2012-10-30 10:27:34  57.98  0 20103   2012-10-31 12:24:42  60.83  2.85 

Answer 1

Working with MySQL variables is great, its like inline program variable assignments. First, the FROM clause "declares" the @ variables for you, defaulting to blank. Then query the records in the expected order you want them. It makes a single pass through the data instead of via repeated subqueries which can be time intensive.

For each row read, compare the @lastSN with the SN of the current record. If different, always return 0. If it IS the same, compute the simple difference. Only AFTER that compare is done, set the @lastSN and @lastValue equal to that of the current record for the next records comparison.

select       EL.SN,       EL.Date,       EL.Value, --remove duplicate alias       if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,       @lastSN := EL.SN,       @lastValue := EL.Value    from       EnergyLog EL,       ( select @lastSN := 0,                @lastValue := 0 ) SQLVars    order by       EL.SN,       EL.Date 

Answer 2

This should do the trick:

SELECT l.sn,        l.date,         l.value,        l.value - (SELECT value                    FROM energylog x                   WHERE x.date < l.date                   AND x.sn = l.sn                   ORDER BY date DESC                   LIMIT 1) consumption FROM energylog l; 

See SQLFiddle: http://sqlfiddle.com/#!2/b9eb1/8