Multiple aggregates - SQL Server Forums

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

Transact-SQL (2000)

Multiple aggregates

Author

Topic

cschl02
Starting Member

3 Posts

Posted - 2006-11-18 : 09:22:58

Hi all. Hopefully this'll be relatively easy for somebody - consider it a noob question.

I'm running into problems with multiple aggregation. My table contains time data for visitor orders. One visitor (unique ID) may submit multiple orders (SUBMITTED), and each order may be completed at a different time (DONE). Potentially all may be done simultaneously). I'd like to figure out the average maximum time for order completion, based on hour of order submission. In other words:

1) For each visit/ID, figure out the longest submit-to-done interval. This is easy.

select max(datediff(minute,submitted,done))
from orderlog
group by ID

2) Now that we have 1, and only 1, interval (the longest) for each visit, group all of those times based on hour of day submitted, and get an average for each hour.

This is where I meet trouble. If I can somehow avoid returning an error from my code attempts, I somehow end up with multiple intervals for many IDs, which is obviously wrong. (I can't explain why.)

Any pointers?

Thanks a lot.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-19 : 00:39:42

You need to give us the code that isn't working if you want help with it, but here is an example of how you could approach this. It's just going to take hours for all dates, you probably want to add more grouping to get days, but I'm not going to guess at what you really want.

select HourSubmitted, avg(maxOrderTime) as AvgMaxTime
from
(select [ID], datepart(hh, submitted) as HourSubmitted
	, max(datediff(minute,submitted,done)) as maxOrderTime
from orderlog
group by datepart(hh, submitted), [ID]) as maxTimes
group by HourSubmitted

cschl02
Starting Member

3 Posts

Posted - 2006-11-19 : 22:59:26

Sorry if I wasn't able to be more clear in my description, but I know it's better than my previously nonfunctional query. In any event, you guessed just right - your query does exactly what I need.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources