CODEWITHSUNDEEP
sqlsql-server-2005group-byaverage
RoboBear
RoboBear

Reputation: 5764

Calculating the AVG value per GROUP in the GROUP BY Clause

I'm working on a query in SQL Server 2005 that looks at a table of recorded phone calls, groups them by the hour of the day, and computes the average wait time for each hour in the day.

I have a query that I think works, but I'm having trouble convincing myself it's right.

SELECT
    DATEPART(HOUR, CallTime) AS Hour,
    (AVG(calls.WaitDuration) / 60) AS WaitingTimesInMinutes
FROM (
    SELECT
        CallTime,
        WaitDuration
    FROM Calls
    WHERE DATEADD(day, DATEDIFF(Day, 0, CallTime), 0) = DATEADD(day, DATEDIFF(Day, 0, GETDATE()), 0)
        AND DATEPART(HOUR, CallTime) BETWEEN 6 AND 18
) AS calls
GROUP BY DATEPART(HOUR, CallTime)
ORDER BY DATEPART(HOUR, CallTime);

To clarify what I think is happening, this query looks at all calls made on the same day as today, and where the hour of the call is between 6 and 18 -- the times are recorded and SELECTed in 24-hour time, so this between hours is to get calls between 6am and 6pm.

Then, the outer query computes the average of the WaitDuration column (and converts seconds to minutes) and then groups each average by the hour.

What I'm uncertain of is this: Are the reported BY HOUR averages only for the calls made in that hour's timeframe? Or does it compute each reported average using all the calls made on the day and between the hours? I know the AVG function has a optional OVER/PARTITION clause, and it's been a while since I used the AVG group function. What I would like is that each result grouped by an hour shows ONLY the average wait time for that specific hour of the day.

Thanks for your time in this.

Upvotes: 1

Views: 7541

Answers (3)

shawnt00
shawnt00

Reputation: 17915

The grouping happens on the values that get spit out of datepart(hour, ...). You're already filtering on that value so you know they're going to range between 6 and 18. That's all that the grouping is going to see.

Now of course the datepart() function does what you're looking for in that it looks at the clock and gives the hour component of the time. If you want your group to coincide with HH:00:00 to HH:59:59.997 then you're in luck.

I've already noted in comments that you probably meant to filter your range from 6 to 17 and that your query will probably perform better if you change that and compare your raw CallTime value against a static range instead. Your reasoning looks correct to me. And because your reasoning is correct, you don't need the inner query (derived table) at all.

Also if WaitDuration is an integer then you're going to be doing decimal division in your output. You'd need to cast to decimal in that case or change the divisor a decimal value like 60.00.

Upvotes: 2

Hogan
Hogan

Reputation: 70523

Yes if you use the AVG function with a GROUP BY only the items in that group are averaged. Just like if you use the COUNT function with a GROUP BY only the items in that group are counted.

You can use windowing functions (OVER/PARTITION) to conceptually perform GROUP BYs on different criteria for a single function.

eg

AVG(zed) OVER (PARTITION BY DATEPART(YEAR, CallTime)) as YEAR_AVG

Upvotes: 2

D Stanley
D Stanley

Reputation: 152556

Are the reported BY HOUR averages only for the calls made in that hour's timeframe?

Yes. The WHERE clause is applied before the grouping and aggregation, so the aggregation will apply to all records that fit the WHERE clause and within each group.

Upvotes: 0

Related Questions