Skip to content
Advertisement

MS SQL return single row case dataset without GROUP BY clause

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'
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement