CODEWITHSUNDEEP
mysqlsql
Vince Carter
Vince Carter

Reputation: 946

MySQL sum each X rows

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 ?

Upvotes: 4

Views: 196

Answers (2)

AssenKhan
AssenKhan

Reputation: 566

This query may help you:

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

Upvotes: 0

sagi
sagi

Reputation: 40491

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 .

Upvotes: 4

Related Questions