CODEWITHSUNDEEP
phpmysqlsql
llaid
llaid

Reputation: 85

PHP query difference between 2 rows MySql table

Hello i am looking for a way to make the difference between 2 rows in Mysql query

datetime             | value
2016-01-04 10:00:00  | 50 
2016-01-04 11:00:00  | 60 
2016-01-04 13:00:00  | 65

The result i m looking for is:

datetime             | value
2016-01-04 10:00:00  | 0 
2016-01-04 11:00:00  | 10 
2016-01-04 13:00:00  | 5

How can i obtain this result by php please?

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method uses a correlated subquery. Note that this will return NULL for the first difference:

select t.*,
       (t.value -
        (select t2.value
         from t t2
         where t2.datetime < t.datetime
         order by t2.datetime desc
         limit 1
        )
       ) as diff
from t;

It is easy enough to convert the NULL to 0, but I prefer the NULL value because the previous value is not meaningful.

Upvotes: 1

Related Questions