1
A friend created a project in Arduino that monitors power consumption in Kw/h. It every 5 minutes inserts the consumption information in the Mysql database.
Every five records I need to average them, and then take the average and multiply by 15.
I can do this in both PHP and Mysql, someone could give me a hint of how to make a listing with all the averages of 5 in 5?
4
To get a listing with averages multiplied by 15:
SELECT AVG( consumo ) * 15 AS media_consumo
FROM leitura
GROUP BY FLOOR( ( id - 1 ) / 5 )
ORDER BY id
See working on SQL Fiddle.
Of curiosity, the average x 15 is the same as the sum multiplied by 3:
SELECT SUM( consumo ) * 3
See working on SQL Fiddle.
Note: this type of grouping only makes sense if the ids are continuous.
I just tested it now, sorry for the delay in returning. It gave the following error: SQL Execution error #1064. Response from the database. You have an error in your SQL syntax. ; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'GROUP BY FLOOR ((id-1)/5)'at line 4 ... returned this message.
thus also did not give: GROUP BY INT(id / 5) , observation,I already changed the question.
@Sucker There was a typo here in the answer, but the demonstration in Sqlfiddle was correct, did you see? Anyway, I now corrected from here. The ORDER position was wrong in the answer.
And I didn’t even notice, I hesitated, I’m sorry. That’s exactly what I needed, thanks for the help. Hugging.
@Otáciobarbosa does not promise, but if you give a little time later, I put a PHP version as an example. It’s quite simple to do, but I don’t like to publish without testing (testing I already screw up sometimes kkk)
By the way, just a curiosity, what would be the reason to multiply by 15? It would not be by 12, to give the value per hour? 60 / 5 = 2.4 and 2.4 * 5 = 12. If it is to calculate per hour, it would be the sum of the 5 multiplied by 2.4 (or the average by 12).
I received the information that Cemig calculates every 15 minutes,doing the calculation 15 I can see the expense and try to base on a spent energy value,thus being able to detail a monitoring panel.
@Otáciobarbosa try to make a general average too, maybe it is simpler and more accurate. I think if I was to calculate every fifteen, I would just take groups of 3 instead of 5. But I could be talking nonsense, since I don’t know details...
@Bacoo sure, I will do all the tests and then I return as was.
0
The average calculation is basically the sum of the elements divided by the number of elements.
Example of average calculation: (1 + 2 + 3 + 4) / 4 = 2,5.
In this sense, in PHP, you can do so:
function myCalc($data = [], $param = 15) {
return (array_sum($data) / count($data)) * $param;
}
And in the SQL query:
select (SUM(campo_numerico) / COUNT(campo_numerico)) * 15) AS calculo from TABELA order by ID desc LIMIT 5;
or
select (AVG(campo_numerico) * 15) as calculo from TABELA order by ID desc LIMIT 5;
Good morning, So it worked the media yes,more I need it to be like @Bacco said to show all the media of db,ie a list of media. Example: the database has 1000 records, every 5 records it will calculate the average,e me lsitas all the averages. In the case of 1000 records it would return me 200 averages.
Browser other questions tagged php mysql arduino
You are not signed in. Login or sign up in order to post.
This should be used to calculate the average of the last 5 readings.
SELECT AVG( consumo ) ORDER BY id DESC LIMIT 5- Now, what’s the point of making the average to multiply? Add it all up and multiply by three, it’s the same.– Bacco
Good evening @Bacco I did,plus he’s bringing the average only 5,what I need is for it to give me an average every 5.
– Chefe Druida
@Bacco Follow select: SELECT AVG(Consumption)AS CONSUMPTION FROM Reading ORDER BY Id DESC LIMIT 5
– Chefe Druida
Do you want a listing with all the averages of 5 out of 5? it would be nice to edit the question and make it clearer if that’s it. The solution would be to add something like a GROUP BY INT(id / 5)
– Bacco