0

I have a table:

Unique ID, DeviceID, Temperature, Epoch

And 3 sensors (DeviceID's) sending temperature every minute to the database.

I would like to select to return AVG(Temperature) for every DeviceID per day since the beggining. So the results should come up as:

10-1-2016 Device1 AVG(Temperature)=32
10-1-2016 Device2 AVG(Temperature)=13
10-1-2016 Device3 AVG(Temperature)=14

10-2-2016 Device1 AVG(Temperature)=13
10-2-2016 Device2 AVG(Temperature)=13
10-2-2016 Device3 AVG(Temperature)=13

10-3-2016 Device1 AVG(Temperature)=22
10-3-2016 Device2 AVG(Temperature)=22
10-3-2016 Device3 AVG(Temperature)=22

Would you be so kind to help out with this please?

Tried following:

SELECT DATE(FROM_UNIXTIME(Epoch)) AS NodeDate, DeviceID as NodeID,AVG(PM1) AS Temperature FROM Data WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE()-7 GROUP BY NodeDate ORDER BY NodeDate ASC

The results are bringing only one DeviceID per day.

enter image description here

  • This is actually quite basic. Read about the GROUP BY clause and if you encounter any problems, please post what you've tried. – tombom Apr 28 '16 at 8:08
  • @tombom, thank you. added my tests with GROUP BY - unfortunately results return only one DeviceID per day – PeeS Apr 28 '16 at 8:15
  • 1
    You have to group by the deviceId, too. Try GROUP BY NodeDate, NodeID. – tombom Apr 28 '16 at 8:58
  • @tombon, perfect you made my day! – PeeS Apr 28 '16 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.