CODEWITHSUNDEEP
mysqlsqlsequential
dasdasd
dasdasd

Reputation: 2031

Mysql - Get the difference between two sequential values

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

Upvotes: 1

Views: 1106

Answers (4)

Mojtaba Rezaeian
Mojtaba Rezaeian

Reputation: 8736

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;

Upvotes: 0

potashin
potashin

Reputation: 44581

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.

Upvotes: 1

Adam
Adam

Reputation: 18845

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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;

Upvotes: 1

Related Questions