troubleshooting Question

Need to combine hourly and daily average data in SELECT

Avatar of James Steinbacher
James SteinbacherFlag for United States of America asked on
Microsoft SQL Server
2 Comments1 Solution24 ViewsLast Modified:
I have a query that averages hi-resolution sensor data across an hour:

SELECT 
   DATEADD(HOUR, DATEDIFF(HOUR, 0, Sensor_Turb0.t_stamp), 0) AS t_stamp_hour
   , AVG(Sensor_Turb0.mbTurb0_Turb) AS Avg_Turb
   , AVG(Sensor_Flow0.mbflow0_Flow) AS Avg_Flow

FROM Sensor_Turb0
  INNER JOIN Sensor_Flow0 ON Sensor_Turb0.t_stamp = Sensor_Flow0.t_stamp

WHERE (Sensor_Turb0.t_stamp > DATEADD(DAY,-1,CAST(GETDATE() AS date))) 

GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, Sensor_Turb0.t_stamp), 0)
ORDER BY DATEADD(HOUR, DATEDIFF(HOUR, 0, Sensor_Turb0.t_stamp), 0)

The result of the query is used to generate a chart:


I need to also include an average line for each pen, resulting in a chart that would look something like this:


I can get the average of either chart pen using a query like this:

SELECT 
   AVG(Sensor_Turb0.mbTurb0_Turb) AS Avg_Turb

FROM Sensor_Turb0

WHERE (Sensor_Turb0.t_stamp > DATEADD(DAY,-1,CAST(GETDATE() AS date))) 

Unfortunately, the charting application I need to use doesn't allow multiple data sources (queries).  Is there a way to get both the hourly average data and the daily average data returned from a single query?

Thank you for looking at this issue!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros