CODEWITHSUNDEEP
sqlsql-server
user3096487
user3096487

Reputation: 399

Hourly Total Average of a Group

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

Desired and Getting

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

Upvotes: 1

Views: 67

Answers (1)

forpas
forpas

Reputation: 164099

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

Upvotes: 1

Related Questions