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]);
