MySQL sums every X lines

Question

I need a little MySQL help. I have a table with hourly values:

ID | VALUE | HOUR 1 | 1.0 | 01.00 2 | 1.0 | 02.00 ... 24 | 1.0 | 24.00 

Now I have found that sometimes the data that I get is not on hourly bases, but on every 30 minutes or sometimes on every 15 minutes. So the data might look like this:

 ID | VALUE | HOUR 1 | 1.0 | 01.00 2 | 1.0 | 01.30 ... 48 | 1.0 | 24.00 

I need to display data in an hourly format, so I'm curious if there is any chance of telling MySQL to summarize each of two (for the format of 30 minutes) or every four (15 minutes) lines of the result?

Answer 1

Yes, you can use FLOOR (Rounds Down) or CEIL (Rounds Up), depending on what you prefer:

 SELECT FLOOR(t.hour) as rounded_hour, SUM(t.value) as your_sum FROM YourTable t GROUP BY FLOOR(t.hour) 

eg.

 1.0 1.2 1.75 1.9 

Everything will turn into 1.

Answer 2

This request may help you:

 Select ID,sum(Value),sum(Hour),substr(hour,1,2)as h group by h;