CODEWITHSUNDEEP
mysql
Nikhil
Nikhil

Reputation: 121

difference between two consecutive rows in mysql

I have a table in MySQL database:

  id     ts            number
1   07-10-2017 00:00    200
2   07-10-2017 00:01    300
3   07-10-2017 00:02    700
4   07-10-2017 00:03    1000

I want to calculate difference between two consecutive rows and i need output format be like: 

id        ts           number   o/p
1   07-10-2017 00:00    200      0
2   07-10-2017 00:01    300     100
3   07-10-2017 00:02    700     400
4   07-10-2017 00:03    1000    300

I want first value of o/p column to be 0 and rest is the difference.

Any help would be appreciated.

Upvotes: 5

Views: 8091

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521854

Use a self-join:

SELECT
    t1.ts,
    t1.number,
    t1.number - COALESCE(t2.number, t1.number) AS diff
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t1.id = t2.id + 1
ORDER BY
    t1.ts;

enter image description here

Demo

Upvotes: 10

A. Colonna
A. Colonna

Reputation: 872

You can do this with a local variable:

SET @tmp := -1;
SELECT id,
 ts ,
 number,
 if(@tmp =-1, 0, number - @tmp) as 'o/p',
 @tmp:=number as dummy
FROM YourTable
Order by ts;

Upvotes: 7

Related Questions