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

Answer 1

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.