Average of rows in an hour for the last 12 hours

Transact-SQL
1

Afternoon all,

Hope everone's keeping well in these strange times! I'm workng on a personal project trying to graph some sensor data, I'm able to get the average data for the last hour but rather stuck on how i might expand this to get the average data for every hour in the last 12 hours. I looked at using a loop but couldn't get the syntax right...
Using MySQL at the moment... Any ideas greatly apreciated...

Thanks

Dave

PS I'm hoping its a simple enough answer you don't need test data, if you do let me know...

SELECT @row_number:=@row_number+1 AS AvID,
	   AVG(a.temp) AS avtemp,
	   AVG(a.voltage) AS avvolt,
       MAX(a.pump1run) AS avp1,
       MAX(a.pump2run) AS avp2,
       MAX(a.pump3run) AS avp3,
       MAX(a.pump4run) AS avp4
FROM (
    	SELECT temp, voltage, pump1run,
		pump2run, pump3run,pump4run,
		timestamp
		FROM testdata 
		WHERE timestamp >= NOW() - INTERVAL 1 HOUR
		ORDER BY TimeStamp DESC 
      ) AS a
2

hi

one idea is to use a tally table ... hope this helps !!! :slight_smile:

image
image874×342 5 KB

3

This is a Microsoft SQL Server web site and not MySQL? Some folks might be proficient in that but I think you will get a quicker answer on a MySQL forum as syntax differs between the sql languages in each of those products?

4

I know & understand, I usually work with SQL Server hence being a member of the forum. I just though i'd ask save me signing up to another forum...