I am trying to find the combined hourly average (travel time in this case) from a group of segments (code in the below) I get can get each segment individually but want to return sum all segments to produce one average for the group. My results give the average of all of the segments instead of the average combined time for all of the segments
SELECT (DATEPART(YEAR, RT1.[Segment_Date])) AS "YEAR", (DATEPART(MONTH, RT1.[Segment_Date])) AS "MONTH", (DATEPART(DD, RT1.[Segment_Date])) AS "DAY", (DATEPART(HH, RT1.[Segment_Date])) AS "HOUR", AVG( CASE WHEN Code IN ( '149242059', '393397506', '393507671', '429235061', '429235101', '429235108', '429235159', '429235160', '429235161', '429255654', '429255655' ) THEN ([TravelTimeMinutes]) ELSE 0 END ) AS "AVERAGE TRAVEL TIME" FROM [Source].[dbo].[Source] RT1 WHERE RT1.[Segment_Date] >= @StartDateTime AND RT1.[Segment_Date] < @EndDateTime GROUP BY (DATEPART(YEAR, RT1.[Segment_Date])),(DATEPART(MONTH, RT1.[Segment_Date])),(DATEPART(DD, RT1.[Segment_Date])),(DATEPART(HH, RT1.[Segment_Date])) ORDER BY (DATEPART(YEAR, RT1.[Segment_Date])),(DATEPART(MONTH, RT1.[Segment_Date])),(DATEPART(DD, RT1.[Segment_Date])),(DATEPART(HH, RT1.[Segment_Date]))
Advertisement
Answer
Move the condition of the CASE
expression to the WHERE
clause.
Then group by Code
also to get the averages of each Code
and use SUM()
window function to get the sum of all averages:
SELECT DISTINCT DATEPART(YEAR, [Segment_Date]) AS [YEAR], DATEPART(MONTH, [Segment_Date]) AS [MONTH], DATEPART(DD, [Segment_Date]) AS [DAY], DATEPART(HH, [Segment_Date]) AS [HOUR], SUM(AVG([TravelTimeMinutes])) OVER ( PARTITION BY DATEPART(YEAR, [Segment_Date]), DATEPART(MONTH, [Segment_Date]), DATEPART(DD, [Segment_Date]), DATEPART(HH, [Segment_Date]) ) AS [AVERAGE TRAVEL TIME] FROM [Source].[dbo].[Source] WHERE [Segment_Date] >= @StartDateTime AND [Segment_Date] < @EndDateTime AND [Code] IN ('149242059', '393397506', '393507671', '429235061', '429235101', '429235108', '429235159', '429235160', '429235161', '429255654', '429255655') GROUP BY [Code], DATEPART(YEAR, [Segment_Date]), DATEPART(MONTH, [Segment_Date]), DATEPART(DD, [Segment_Date]), DATEPART(HH, [Segment_Date]) ORDER BY DATEPART(YEAR, [Segment_Date]), DATEPART(MONTH, [Segment_Date]), DATEPART(DD, [Segment_Date]), DATEPART(HH, [Segment_Date]);