PHP query difference between 2 rows MySql table

Question

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?

Please give us your code to work with, both PHP and MySQL.

marklark
– marklark

2016-04-11 14:39:56 +00:00
Commented Apr 11, 2016 at 14:39 — marklark
– marklark, Commented Apr 11, 2016 at 14:39

Gordon Linoff · Accepted Answer · 2016-04-11 14:38:48Z

1

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.

answered Apr 11, 2016 at 14:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dávid Horváth Over a year ago

Order-by is also necessary in the outer query.

llaid Over a year ago

Perfect ! Thanks Guys

Collectives™ on Stack Overflow

PHP query difference between 2 rows MySql table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related