group by issue in sql

i’m trying to get in a new column the sessions who are between 08:00 and 18:00. You can see my last CASE in the CTE. For each date there should be a new column “TotalRestrictedSessions” which indicate how many session were on that particular date. If there are none, in this case i have to write 0. I suspect that my problem is when i convert the DATE?

WITH ParkeonCTE
AS
(
SELECT 
    OccDate = CONVERT(DATE, OC.LocalStartTime),
    TotalOccSessions = COUNT(OC.SessionId),
    AuthorityId,
    TotalOccDuration = ISNULL(SUM(OC.DurationMinutes),0),
    TotalNumberOfOverstay = SUM(CAST(OC.IsOverstay AS INT)),
    TotalMinOfOverstays = ISNULL(SUM(OC.OverStayDurationMinutes),0),
    (CASE
        WHEN OC.OspId IS NULL THEN 'OffStreet' ELSE 'OnStreet'
        END
    ) AS ParkingContextType,
    (CASE
        WHEN CAST(OC.LocalStartTime AS TIME) >= '08:00:00' AND CAST(OC.LocalStartTime AS TIME) <= 
 '18:00:00'
            THEN COUNT(OC.SessionId)
     END
    ) AS TotalRestrictedSessions
FROM Analytics.OccupancySessions AS OC
WHERE OC.AuthorityId IS NOT NULL
GROUP BY  CONVERT(DATE,OC.LocalStartTime), OC.AuthorityId,OC.OspId
)
SELECT OC.OccDate,
   OC.ParkingContextType,
   OC.AuthorityId,
   OC.TotalRestrictedSessions,
   SUM(OC.TotalOccSessions) AS TotalOccSessions,
   AVG(OC.TotalOccDuration) AS AvgOccMinutesDuration, -- wrong
   SUM(OC.TotalOccDuration) AS TotalOccDuration,
   SUM(OC.TotalNumberOfOverstay) AS TotalNumberOfOverstay,
   SUM(OC.TotalMinOfOverstays) AS TotalMinOfOverstays,
   CAST(AVG(OC.TotalMinOfOverstays) AS decimal(10,2)) AS AvgMinOfOverstays -- wrong
  FROM ParkeonCTE AS OC
GROUP BY OC.OccDate, OC.AuthorityId, OC.ParkingContextType
ORDER BY OC.OccDate DESC

Answer

You just need to move your aggregation outside of your CASE expression, called conditional aggregation.

  SUM(CASE
        WHEN CAST(OC.LocalStartTime AS TIME) >= '08:00:00' 
        AND CAST(OC.LocalStartTime AS TIME) <= '18:00:00'
         THEN 1
         ELSE 0
      END
    ) AS TotalRestrictedSessions

Generally, you should include the current query results and your desired results in your question to make it easier to figure out where the issues are.

Advertisement

Answer