Avg per hour MySQL

Navigation
0

Hello, I would like to know how to return only the hourly averages of a table as below: 

id | valor |     timestamp    |
1  | 5.4   | 2017-4-20 18:00  |
2  | 3     | 2017-4-20 18:01  |
3  | 2.8   | 2017-4-20 18:40  |
4  | 2     | 2017-4-20 19:00  |
5  | 10    | 2017-4-20 19:05  |
6  | 7     | 2017-4-20 19:20  |
7  | 1     | 2017-4-20 19:55  |
8  | 5     | 2017-4-20 20:00  |
9  | 7     | 2017-4-20 20:11  |
10 | 4     | 2017-4-20 20:50  |
11 | 6     | 2017-4-20 21:00  |
------------------------------

.. would I have some select to return the averages per hour, from the "value" column? I'm trying but unsuccessful ...

    
asked by anonymous 21.04.2017 / 23:45

2 answers

1
SELECT AVG( valor ) , HOUR( 'timestamp' )
FROM tabela
WHERE DATE_SUB(  'timestamp' , INTERVAL 1 HOUR )
GROUP BY HOUR( 'timestamp' )

Result

  

Totaketheaveragesperhourofeveryday

SELECTAVG(valor),HOUR('timestamp'),day('timestamp')FROMtabelaWHEREDATE_SUB('timestamp',INTERVAL1HOUR)GROUPBYHOUR('timestamp'),day('timestamp')id|valor|timestamp|1|5.4|2017-4-2018:00|2|3|2017-4-2018:01|3|2.8|2017-4-2018:40|4|2|2017-4-2019:00|5|10|2017-4-2019:05|6|7|2017-4-2019:20|7|1|2017-4-2019:55|8|5|2017-4-2020:00|9|7|2017-4-2020:11|10|4|2017-4-2020:50|11|6|2017-4-2021:00|12|4|2017-4-2121:08|13|6|2017-4-2121:10|14|3|2017-4-2121:25|15|6|2017-4-2122:15|16|4|2017-4-2122:25|17|4|2017-4-2122:35|------------------------------

    
22.04.2017 / 01:48
0

You can use the AVG aggregation function: 

SELECT timestamp, AVG(valor)
->        FROM tabela
->        GROUP BY timestamp;

To learn more about aggregation functions: link

    
22.04.2017 / 00:30
MyViewHolder with error PHP PDO - MySQL query data does not appear in the table