# 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

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';
```

```

# Demo

0

source

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

All Articles