I have a data set of ActivityDate which is a datetime field and EngineHours which is a decimal field. I want to return a single row of data that brings back the average number of EngineHours on each day of the week. The dataset is pretty big so the best performance is likely to be the most ideal, the script below doesn’t work because ActivityDate is not contained in either an aggregate or GROUP BY clause:
SELECT CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Monday' THEN AVG(CS.EngineHours) END AS MondayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Tuesday' THEN AVG(CS.EngineHours) END AS TuesdayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Wednesday' THEN AVG(CS.EngineHours) END AS WednesdayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Thursday' THEN AVG(CS.EngineHours) END AS ThursdayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Friday' THEN AVG(CS.EngineHours) END AS FridayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Saturday' THEN AVG(CS.EngineHours) END AS SaturdayEngineHours, CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Sunday' THEN AVG(CS.EngineHours) END AS SundayEngineHours FROM CustomerSummary CS WITH(NOLOCK) WHERE ActivityDate >= '2021-02-01' AND ActivityDate <= '2021-02-07'
Advertisement
Answer
Close. The CASE
is the argument to the AVG()
:
SELECT AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Monday' THEN CS.EngineHours END) AS MondayEngineHours, AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Tuesday' THEN CS.EngineHours END) AS TuesdayEngineHours, AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Wednesday' THEN CS.EngineHours END) AS WednesdayEngineHours, AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Thursday' THEN CS.EngineHours END) AS ThursdayEngineHours, AVG(CASE WHEN DATENAM)E(dw, CS.ActivityDate) = 'Friday' THEN CS.EngineHours END AS FridayEngineHours, AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Saturday' THEN CS.EngineHours END) AS SaturdayEngineHours, AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Sunday' THEN CS.EngineHours END) AS SundayEngineHours FROM CustomerSummary CS WHERE ActivityDate >= '2021-02-01' AND ActivityDate <= '2021-02-07'