Difference between results MySQL

Question

I have the following a query that shows the following results:

SELECT 
  meter.meter_serial,
  her060.node_address,
  her060.report_time,
  her060.node_reading 
FROM
  meter,
  her060 
WHERE meter.node_address = her060.node_address 
  AND meter.meter_serial = '31602193' 
ORDER BY her060.report_time DESC 
LIMIT 10

And the results are fine:

meter_serial    node_address    report_time           node_reading
31602193    10164d995147    10/01/2014 0:36           777
31602193    10164d995147    8/01/2014 18:22           763
31602193    10164d995147    7/01/2014 6:04            724
31602193    10164d995147    6/01/2014 12:19           698
31602193    10164d995147    4/01/2014 23:55           669
31602193    10164d995147    3/01/2014 11:30           650
31602193    10164d995147    1/01/2014 23:06           624
31602193    10164d995147    31/12/2013 10:41          624
31602193    10164d995147    29/12/2013 22:17          624
31602193    10164d995147    28/12/2013 9:53           624

But I wanted to add another column that calculates the difference between the node_readings.

Can anyone suggest what to do?

you mean the difference in node_reading between different rows ? — cardeol, Jan 21 '14 at 10:30
Does the difference between node_readings mean the difference between the current and the previous nod_reading? — Mihai Stancu, Jan 21 '14 at 10:30
possible duplicate of [MySQL difference between two rows of a SELECT Statement](http://stackoverflow.com/questions/14857159/mysql-difference-between-two-rows-of-a-select-statement) — M Khalid Junaid, Jan 21 '14 at 10:35
yeah sorry I mean the difference between each row in date order for example the difference between row 1 and 2 would be 14 and so on — RyanGest, Jan 21 '14 at 10:45

fancyPants · Accepted Answer · 2014-01-21T11:58:25.493

1

SELECT 
  meter.meter_serial,
  her060.node_address,
  her060.report_time,
  her060.node_reading,
  @previous - node_reading AS your_difference, /*here the variable holds the value of the previous row*/
  @previous := node_reading  /*here the variable gets assigned the value of the current row*/
FROM
  meter
INNER JOIN her060 ON meter.node_address = her060.node_address 
CROSS JOIN (SELECT @previous := (SELECT node_reading FROM her060 ORDER BY report_time DESC LIMIT 1)) variable_initialization_query_alias
WHERE
   meter.meter_serial = '31602193' 
ORDER BY her060.report_time DESC 
LIMIT 10

edited Jan 21 '14 at 11:58

answered Jan 21 '14 at 11:33

fancyPants

50,732
33
89
96

This looks really close but I think I am missing something. when I run it I get the same value in both node_reading and previous which also means I get NULL in your_difference – RyanGest Jan 21 '14 at 11:50
A simple `:` was missing, corrected it. Please try again. – fancyPants Jan 21 '14 at 11:58
Nice one works perfectly thanks heaps for your help – RyanGest Jan 21 '14 at 12:01

score 0 · Answer 2 · answered Jan 21 '14 at 11:08

0

Use autoincrement field or unique key for this column so that you can subtract previous row value.

**SELECT   meter.meter_serial,
  her060.node_address,
  her060.report_time,
  her060.node_reading ,
  a.node_reading -b.node_reading 
FROM
  meter,
  her060 a
 inner join 
her060 
 on a.id = b.id -1
WHERE meter.node_address = her060.node_address 
  AND meter.meter_serial = '31602193' 
ORDER BY her060.report_time DESC 
LIMIT 10**

answered Jan 21 '14 at 11:08

Aman Aggarwal

17,619
9
53
81

1

no way to use an auto increment because there are other meter_serial in the table and no way of knowing what order they come in – RyanGest Jan 21 '14 at 11:16

Difference between results MySQL

2 Answers2