Select grouping by average hour and sum per day

Question

How can I return in a select the daily sum of the value column, Since before adding up I have to average an hour? My table receives the "value" once a second, I need to make the average per hour of this value and return the sum of these averages per day...

I use Mysql.

valor         time
15      2017-10-13 14:02:06
99      2017-10-13 12:26:57
75      2017-09-25 21:20:08
40      2017-09-25 21:19:32
8       2017-09-16 19:23:16
5       2017-09-16 19:20:48
2.5     2017-09-16 19:20:35
2       2017-08-26 13:46:13
1.41    2017-08-26 13:45:13
1.41    2017-08-26 13:45:12
1.41    2017-08-26 13:45:11
1.41    2017-08-26 13:45:09
1.41    2017-08-26 13:45:08
1.41    2017-08-26 13:45:07
1.41    2017-08-26 13:45:06
1.41    2017-08-26 13:45:05
1.41    2017-08-26 13:45:04
1.41    2017-08-26 13:45:03
1.41    2017-08-26 13:45:02
1.41    2017-08-26 13:45:01
1.41    2017-08-26 13:45:00
1.41    2017-08-26 13:44:59
1.41    2017-08-26 13:44:58
1.41    2017-08-26 13:44:57
1.41    2017-08-26 13:44:55
1.41    2017-08-26 13:44:54
1.41    2017-08-26 13:44:53
1.41    2017-08-26 13:44:52
1.41    2017-08-26 13:44:51
1.41    2017-08-26 13:44:50
1.41    2017-08-26 13:44:49
1.41    2017-08-26 13:44:48
1.41    2017-08-26 13:44:47
1.41    2017-08-26 13:44:46
1.41    2017-08-26 13:44:45
1.41    2017-08-26 13:44:44
1.41    2017-08-26 13:44:42
1.41    2017-08-26 13:44:41
1.41    2017-08-26 13:44:40
1.41    2017-08-26 13:44:39
1.41    2017-08-26 13:44:38
1.41    2017-08-27 13:44:37
1.41    2017-08-27 13:44:36
1.41    2017-08-27 13:44:35
1.41    2017-08-27 13:44:34
1.41    2017-08-27 13:44:33
1.41    2017-08-27 13:44:32
1.41    2017-08-27 13:44:31
1.41    2017-08-27 13:44:30
1.41    2017-08-27 13:44:29

Answer 1

Select the average per day:

select
    avg(valor) as media,
    cast(data_hora as date) as dia
from valores
group by cast(data_hora as date);

Select the average per day and hour:

select
    avg(valor) as media,
    cast(data_hora as date) as dia,
    hour(data_hora) as hora
from valores
group by cast(data_hora as date),hour(data_hora);

Select from the sum of the averages per day and hour, grouped by day:

select 
    sum(x.media),
    x.dia
from 
    (select
        avg(valor) as media,
        cast(data_hora as date) as dia,
        hour(data_hora) as hora
     from valores
     group by cast(data_hora as date),hour(data_hora)) x
group by x.dia

In particular, I see no application in the sum of the averages... but it is as requested.

I put in Sqlfiddle: http://sqlfiddle.com/#! 9/25fa97/4

Reference:

Function Hour(): https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_hour

Function Cast(): https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast

Function AVG(): https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_avg

Function SUM(): https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_sum