\$100,000.00 Question.

1. Senior Member
Join Date
Dec 1969
Posts
450

\$100,000.00 Question.

Hey folks and welcome to another addition of the \$100,000.00 Question. I&#039;m your host, Mr. Woodson.. Let&#039;s move on to today&#039;s question shall we? Splendid.<BR><BR>Topic: SQL SELECT STATEMENT AND THE USE OF AGGREGATE FUNCTIONS<BR><BR>Okay... here we go:<BR><BR> "SELECT AVG(REFCOMPLETEDTIME) AS RCTAVG FROM ISSUES WHERE RCCODE = &#039;" & RS("CUID") & "&#039;"<BR><BR>The above select staement is asking to take the AVG rep completion time where the RCCODE (location var) is equal to that of the reps. <BR><BR>Basically I want to take the avg of the reps completion times per record, and analyse their results by the overall group results...<BR><BR>How do I take a unique ID average all their record&#039;s completiontime column and then do the same for the whole table to show the comparison...<BR><BR>Jane completes her records in 15 minutes 30 seconds<BR>Overall average is 13 minutes and 15 seconds.<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: \$100,000.00 Question.

"SELECT AVG(REFCOMPLETEDTIME) AS RCTAVG FROM ISSUES WHERE RCCODE = &#039;" & RS("CUID") & "&#039;<BR><BR>UNION SELECT AVG(REFCOMPLETEDTIME) AS RCTAVG FROM ISSUES" <BR><BR>will return two records avage for Jane and total average

3. Senior Member
Join Date
Dec 1969
Posts
96,118

Does that mean that you will keep adding \$100K each time?<BR><BR>[I think the word you are looking for is "edition"?]<BR><BR>And I think two separate queries will give you the best performance. It can be done in one query, but the SQL is ugly and likely quite a bit slower than using the built-in capabilities.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

You need a discriminant...

...because there is no guarantee of what order the records from a UNION will be returned in.<BR><BR>So, maybe:<BR><BR>SELECT RCName, AVG(refCompletedTime) AS RCTAVG <BR>FROM issues WHERE RCCODE = &#039;xxx&#039; GROUP BY RCName<BR>UNION<BR>SELECT &#039;*&#039;, AVG(refCompletedTime) FROM issues <BR><BR>Then RS("RCName") will give you either the person&#039;s name or just an asterisk, to tell you which average you are seeing.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
11,247

Oooo right you are <eop>

.

6. Senior Member
Join Date
Dec 1969
Posts
450

Thanks guys....

I&#039;m in three places at once presently... will try it out and see how it goes...

7. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: Thanks guys....

Make my \$50,000,000 payable too ....

8. Senior Member
Join Date
Dec 1969
Posts
96,118

You want him to write me a check and then write one for you, [hl="yellow"]too[/hl]?<BR><BR>&#060;grin style="evil" /&#062;<BR><BR>[I won&#039;t tell you how many typos I made in various posts yesterday. But I just liked this one for its relevance.]<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
11,247

The hand is faster than the eye

Post button click happy <BR>spill chucker knot warking!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•