Select grouping by average per hour and sum per day

Question

Select grouping by average per hour and sum per day

Navigation

#1 by (5 votes)

2

How can I return a select the daily sum of the value column, being that before adding I have to do an average per hour? My table receives the "value" once per second, I need to average the hourly value of that 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

mysql sql sql-select

asked by anonymous 26.10.2017 / 23:56

1 answer

# 1264 - Out of range value for column 'year' at row 1 Why are the pull-left and pull-right classes no longer working in Bootstrap 4?

score 5 · Accepted Answer

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 time:

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 the sum of the averages by day and time, grouped by the 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

Particularly, I do not see application in the sum of the averages ... but it is as it requested.

I put it in SQLFiddle: link

Reference:

Function Hour() : link

Function Cast() : link

Function AVG() : link

Function SUM() : link