Mysql - Get the difference between two sequential values

Question

I want to get the difference between two sequential values from my table.

| id | count |
| 1  |   1   |
| 2  |   7   |
| 3  |   9   |
| 4  |   3   |
| 5  |   7   |
| 6  |   9   |

For example the difference between

id2-id1 = 6,
id3-id2 = -2,
...

How can I do it? SELECT SUM(id(x+1) - id(x)) FROM table1

You might want to see if http://stackoverflow.com/questions/20849098/mysql-how-get-value-in-previous-row-and-value-in-next-row will help out. — Brian Hoover, Feb 11 '15 at 20:40

score 1 · Answer 1 · answered Feb 11 '15 at 20:46

If you know that the ids have no gaps, then just use a join:

select t.*, (tnext.count - t.count) as diff
from table t join
     table tnext
     on t.id = tnext.id - 1;

If you just want the sum of the differences, then that is the same as the last value minus the first value (all the intermediate values cancel out in the summation). You can do this with limit:

select last.count - first.count
from (select t.* from table order by id limit 1) as first cross join
     (select t.* from table order by id desc limit 1) as last;

potashin · Accepted Answer · 2015-02-11T21:40:24.283

You can use a subquery to find count for the preceding id.

In case there are no gaps in the ID column:

SELECT CONCAT(t.`id` ,' - ', t.`id` - 1) AS `IDs`
     , t.`count` - (SELECT `count`
                    FROM `tbl` 
                    WHERE `id` = t.`id` - 1) AS `Difference`
FROM `tbl` t 
WHERE t.`id` > 1

SQLFiddle

In case there are gaps in the IDcolumn. First solution, using ORDER BY <...> DESC with LIMIT 1:

SELECT CONCAT(t.id ,' - ', (SELECT `id` FROM tbl WHERE t.id > id ORDER BY id DESC LIMIT 1)) AS IDs
     , t.`count` - (SELECT `count` 
                    FROM tbl 
                    WHERE t.id > id
                    ORDER BY id DESC
                    LIMIT 1) AS difference
FROM tbl t
WHERE t.id > 1;

SQLFiddle

Second solution, using another subquery to find count with the MAX(id) less than current id:

SELECT CONCAT(t.id ,' - ', (SELECT MAX(`id`) FROM tbl WHERE id < t.id)) AS IDs
     , t.`count` - (SELECT `count`
                    FROM tbl
                    WHERE `id` = (SELECT MAX(`id`)
                                  FROM tbl
                                  WHERE id < t.id)
                   ) AS difference
FROM tbl t
WHERE t.id > 1;

SQLFiddle

P.S. : First column, IDs, is just for readability, you can omit it or change completely, if it is necessary.

but what if the ids have gaps – dasdasd Feb 11 '15 at 21:01 — dasdasd, Feb 11 '15 at 21:01

score 0 · Answer 3 · answered Feb 11 '15 at 20:56

0

Try this:

SELECT MAX(count)-MIN(count) diff WHERE id IN(1,2)

Or this way

SELECT 2*STD(count) diff WHERE id IN(1,2)

answered Feb 11 '15 at 20:56

Adam

17,838
32
54

score 0 · Answer 4 · answered Jul 14 '15 at 10:20

0

This works even if ids have distances between them:

SELECT *, 
  ((SELECT value FROM example e2 WHERE e2.id > e1.id ORDER BY id ASC LIMIT 1) - value) as diff
FROM example e1;

answered Jul 14 '15 at 10:20

Mojtaba Rezaeian

8,268
8
31
54

Mysql - Get the difference between two sequential values

4 Answers4