Question: MySQL sum each X rows

Question

MySQL sum each X rows

Answers 2
Added at 2017-01-04 14:01
Tags
Question

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

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 which i get is not on hourly bases but it is for every 30 Minutes or sometimes for every 15 Minutes. So the data can 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 the data in hourly format, so I am curious if there is any chance to tell MySQL to sum each two (for 30 Minutes format) or each four (15 Minutes format) rows of the result ?

Answers to

MySQL sum each X rows

nr: #1 dodano: 2017-01-04 15:01

Yes, you can use FLOOR(Rounds down) or CEIL(Rounds up) , depends what you prefer :

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

E.G.

1.0
1.2
1.75
1.9

Will all turn into 1 .

nr: #2 dodano: 2017-01-04 15:01

This query may help you:

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