Calculate difference between previous and current record in Mysql
How to calculate the difference between the current and the previous record.
Here is an example table
| 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 get 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. Take a look at the table. Every time the table contains three data with a separate resource ID. How do you find the difference?
+3
source to share
2 answers
Make sure the table has a diff column. Then, whenever you update 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 and WORKED
Demo
0
source to share
You can use PHP, when you iterate over your result set, store the value in a temporary varariable. use this to compare against the next line, assuming the data you received are integers.
$previousRow = 0; //initiates value for preceeding row
while($row = mysql_fetch_assoc($result)){
$currentRow = $row['data']; //gets current row
//gets difference between the two variables
$difference = $currentRow - $previousRow;
echo "\$difference = $difference<br>\n";
$previousRow = $currentRow; //reassigns previous row
//display otherfield
//display $diff
}
-1
source to share