Group by hour in mysql but include the first value from the hour+1 also
I'm a big fan of Stack Overflow and I tend to contribute regularly (am currently in the top 0.X%). In this category (stackoverflow) of posts I will will be posting my top rated questions and answers. This, btw, is allowed as explained in the meta thread here.
My question was:
Say you have data like this:
time value 10:00 5 10:15 12 10:30 15 10:45 27 11:00 29
And a query like this:
SELECT MAX(value)- MIN(value), HOUR(time)FROM mytable GROUP BY HOUR(time);
You will get:
value time 22 10
But, I would need it to include the value at 11:00, thus the result being 24 (29-5), and not 22. Is there a way to do this in SQL or do I have no other choice than to do this manually in the code level (so, without the grouping, just fetching the data and manually substracting).
The answer, by ATP_JD, was:
Depending on how consistent your data is, you might be able to do this with a self join, like so:SELECT HOUR(a.`time`)AS grouper, GREATEST(MAX(a.value),IFNULL(MIN(b.value),0))- MIN(a.value)AS diff FROM mytable a LEFTJOIN mytable b ONIF(HOUR(a.time)<=23, HOUR(a.time)+1,0)= HOUR(b.time)GROUP BY grouper
LEFT JOINon the same table allows you to get the next hour's value for comparison purposes.