Grouping on a agragate function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping on a agragate function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-21 : 08:26:18

Phillip Grobler writes "I have a table CallRecord :
CREATE TABLE CallRecord (
StartTime datetime,
Duration int
)

I whant to extract number of calls and Total duration for the hour in the day with the maximum number of calls

I have query that gives me the number of calls and durations for each day and hour :
-- All the calls
SELECT convert(varchar(15),StartTime,111) as day, DATEPART(hh, StartTime) as hour, COUNT(*) as 'Calls', SUM(Duration) as Duration,
AVG(Duration) as 'AVG Duration'
FROM CallRecord
GROUP BY convert(varchar(15),StartTime,111), DATEPART(hh, StartTime)
ORDER BY convert(varchar(15),StartTime,111), DATEPART(hh, StartTime)

How do I restrict it to just the entries with the maximum value for calls for each hour get the number ?"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-21 : 10:12:41

I'm unclear how you calculate value, but I guess you mean duration.
You could use a derived table of your original query, like so:

SELECT MAX(Duration), day, hour
FROM
(
--All the calls
SELECT convert(varchar(15),StartTime,111) as day, DATEPART(hh, StartTime) as hour, COUNT(*) as 'Calls', SUM(Duration) as Duration,
AVG(Duration) as 'AVG Duration'
FROM CallRecord
GROUP BY convert(varchar(15),StartTime,111), DATEPART(hh, StartTime)
ORDER BY convert(varchar(15),StartTime,111), DATEPART(hh, StartTime)
)
GROUP BY day,hour

Edited by - YellowBug on 06/21/2002 10:33:34

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 10:16:15

I've replaced CallRecord with #CallRecord below, try:


select convert(varchar(15),starttime,111) as day, datepart(hh, starttime) as hour, count(*) as calls, sum(duration) as duration, avg(duration) as 'avg duration' 
from #callrecord c1 
	inner join (
		select [day] as [day], max(calls) as calls
		from (
			select convert(varchar(15),starttime,111) as day, datepart(hh, starttime) as hour, count(*) as calls
			from #callrecord 
			group by convert(varchar(15),starttime,111), datepart(hh, starttime) ) c
		group by [day] ) c2 on ( convert(varchar(15),starttime,111) = c2.[day] )
group by convert(varchar(15),starttime,111), datepart(hh, starttime), c2.calls
having count(*) = c2.calls
order by convert(varchar(15),starttime,111), datepart(hh, starttime)

I consult on database issues in the telecom industry, so feel free to email me if you have any other questions or if you want to explore a professional relationship.

setBasedIsTheTruepath
<O>

Edited by - setbasedisthetruepath on 06/21/2002 10:43:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources