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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-21 : 08:55:20
|
| Roy writes "We have a table with users and elapsed time. We have 200 users, who will complete multiple processes in some time interval. So its possible to have a user with 500 row entries with different times.It is east to run avg and that will tell me the users average time for his entries. What I need is his average total time compared to all the other users. I can take the sum(total_elapsed_time) and find out the total time for everyone. Then in a separate query I can group the users and find out the sum(users_total_time).What I need to produce is the following by user percentage_elapsed_time = 100 * (users_total_time)/(total_elapsed_time). The process has to be as automatic as possible. I can do it with Access, but it doesn't fly with one sql script in MS SQL7.The second part is worse - to count the users and find the average for the 90%, 80%, 70% and 50% percentiles. In other words if there is 180 users I need to find starting with the shortest time user - to the .8(180) = 144th user the average for this group - this will allow us to look at the first percentage printout and determine where the cutoff for the lowest 80% percentile of the users is.I have tried subselects and declaring variable which work separately but not together." |
|
|
|
|
|
combining sum and avg