March 13, 2012
Calculate difference between previous and current record in Mysql
Question by Deepak
How to calculate the difference between current and previous record.
Here is the table example
| rid | time | data |
|10000038| 2012-03-13 12:30:18 | 100 |
|10000052| 2012-03-13 12:30:18 | 120 |
|10000053| 2012-03-13 12:30:18 | 140 |
|10000038| 2012-03-13 12:20:18 | 160 |
|10000052| 2012-03-13 12:20:18 | 180 |
|10000053| 2012-03-13 12:20:18 | 160 |
|10000038| 2012-03-13 12:10:18 | 100 |
|10000052| 2012-03-13 12:10:18 | 160 |
|10000053| 2012-03-13 12:10:18 | 160 |
Here I would like to have the result as,
| rid | time | data | DIf |
|10000038| 2012-03-13 12:30:18 | 100 | 0 |
|10000052| 2012-03-13 12:30:18 | 120 | 20 |
|10000053| 2012-03-13 12:30:18 | 140 | 20 |
|10000038| 2012-03-13 12:20:18 | 160 | 20 |
|10000052| 2012-03-13 12:20:18 | 180 | 20 |
|10000053| 2012-03-13 12:20:18 | 160 |-20 |
|10000038| 2012-03-13 12:10:18 | 100 |-60 |
|10000052| 2012-03-13 12:10:18 | 160 | 60 |
|10000053| 2012-03-13 12:10:18 | 160 | 0 |
Note: Look at the table, Every time there is three data dumped in the table with seperate resource id. How to find the difference?
Answer by Starx
Make sure you have a diff column in the table. Then, whenever you are updating the table with new values do this
UPDATE
`tablename`
SET
data = 120,
diff = 120 -
(
SELECT prev_data
FROM (
SELECT data AS prev_data
FROM tablename
WHERE `id` = '1'
)
AS prev_data
)
WHERE
id='1';
Tested & WORKS
Demo
