'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'
sqlsql-server


Solution 1:[1]

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'

Solution 2:[2]

The aggregation function must wrap the case

         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 EN)D AS TuesdayEngineHours,
            AVG(CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Wednesday' THEN CS.EngineHours E)ND AS WednesdayEngineHours,
           AVG( CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Thursday' THEN CS.EngineHours END )AS ThursdayEngineHours,
           AVG( CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Friday' THEN CS.EngineHours END A)S FridayEngineHours,
           AVG( CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Saturday' THEN CS.EngineHours E)ND AS SaturdayEngineHours,
           AVG( CASE WHEN DATENAME(dw, CS.ActivityDate) = 'Sunday' THEN CS.EngineHours END A)S SundayEngineHours
        FROM )
            CustomerSummary CS WITH(NOLOCK)
        WHERE
            ActivityDate >= '2021-02-01'
        AND
            ActivityDate <= '2021-02-07'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 Charlieface

Related Questions