{"id":1280,"date":"2015-03-10T06:56:33","date_gmt":"2015-03-10T06:56:33","guid":{"rendered":"http:\/\/www.nikola-breznjak.com\/blog\/?p=1280"},"modified":"2015-08-10T06:49:16","modified_gmt":"2015-08-10T06:49:16","slug":"group-by-hour-in-mysql-but-include-the-first-value-from-the-hour1-also","status":"publish","type":"post","link":"https:\/\/nikola-breznjak.com\/blog\/stack-overflow\/group-by-hour-in-mysql-but-include-the-first-value-from-the-hour1-also\/","title":{"rendered":"Group by hour in mysql but include the first value from the hour+1 also"},"content":{"rendered":"

\"profile<\/a>
\nI’m a big fan of Stack Overflow<\/a> and I tend to contribute regularly (am currently in the top 0.X%<\/a>).\u00a0In this category (stackoverflow<\/a>)\u00a0of posts I will will be posting my top rated questions and answers. This, btw, is allowed as explained in the meta thread here<\/a>.<\/p>\n

My question<\/a> was:<\/p>\n

Say you have data like this:<\/p>\n

  time  value\r\n  10:00   5\r\n  10:15   12\r\n  10:30   15\r\n  10:45   27\r\n  11:00   29<\/pre>\n
And a query like this:<\/p>\n
SELECT MAX(value)- MIN(value), HOUR(time)FROM mytable GROUP BY HOUR(time);<\/pre>\n
You will get:<\/p>\n
 value  time\r\n   22   10<\/pre>\n
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).<\/p>\n
The answer, by ATP_JD<\/a>, was:<\/p>\n
\n
\n
Depending on how consistent your data is, you might be able to do this with a self join, like so:<\/p>\n
SELECT HOUR(a.`time`)AS grouper,\r\nGREATEST(MAX(a.value),IFNULL(MIN(b.value),0))- MIN(a.value)AS diff\r\nFROM mytable a\r\nLEFTJOIN mytable b ONIF(HOUR(a.time)<=23, HOUR(a.time)+1,0)= HOUR(b.time)GROUP BY grouper<\/pre>\n
The\u00a0LEFT JOIN<\/code>\u00a0on the same table allows you to get the next hour’s value for comparison purposes.<\/p>\n
http:\/\/sqlfiddle.com\/#!9\/fe72a\/16<\/a><\/p>\n<\/div>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
I’m a big fan of Stack Overflow and I tend to contribute regularly (am currently in the top 0.X%).\u00a0In this category (stackoverflow)\u00a0of posts I will will be posting…<\/p>\n","protected":false},"author":1,"featured_media":609,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-1280","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-stack-overflow"],"_links":{"self":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/1280","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/comments?post=1280"}],"version-history":[{"count":3,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/1280\/revisions"}],"predecessor-version":[{"id":2021,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/posts\/1280\/revisions\/2021"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/media\/609"}],"wp:attachment":[{"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/media?parent=1280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/categories?post=1280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nikola-breznjak.com\/blog\/wp-json\/wp\/v2\/tags?post=1280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}