Average MYSQL per minute

Question

Average MYSQL per minute

Navigation

#1 by (1 votes)

0

Hello, in the table below, you have to do a select that returns me the following: select the average current per minute between the day x and day y and that on the return display the day and minutes?

I have one record per second in the table, and I need to have the average per minute of these records within a date range.

corrente|        time
2.23    |   2017-04-24 21:22:37
2.23    |   2017-04-24 21:22:38
2.23    |   2017-04-24 21:22:39
1.27    |   2017-04-24 21:23:01
2.25    |   2017-04-25 21:23:02
0.50    |   2017-04-25 21:22:40
2.25    |   2017-04-25 21:22:41
1.02    |   2017-04-26 00:22:41
2.29    |   2017-04-26 00:22:42
2.29    |   2017-04-26 23:22:43
2.24    |   2017-04-26 23:22:43
2.27    |   2017-04-27 14:22:44
2.27    |   2017-04-27 14:22:45
2.24    |   2017-04-27 02:22:46
2.30    |   2017-04-27 02:22:47
2.28    |   2017-04-28 21:22:50
2.28    |   2017-04-28 21:22:51
-------------------------------

sql mysqli

asked by anonymous 28.06.2017 / 01:51

1 answer

I can not create a paragraph! HTML [closed] Error when starting a new project Xamarin

score 1 · Accepted Answer

@Jovani, in just a SELECT we could do that.

The idea is to group all the records of the same date, time and minute. Then divide the sum of the currents by the number of currents in the group:

SELECT DATE_FORMAT(a.'hora', '%d/%m/%Y %H:%i') hora, FORMAT(SUM(a.'corrente') / COUNT(a.corrente), 2) media FROM sua_tabela a
GROUP BY DATE_FORMAT(a.'hora', '%d/%m/%Y %H:%i');

NOTE: In place of your_table, place the name of your table. NOTE: Since TIME is a reserved MySql word, I have decided to replace it by the hour, but if you want to keep the time, just replace all the words SELECT time per time.