How to get the average of hours when one divisor is numeric and the other is text?

Maybe using sql would be a cleaner way.
Two problems, though.
1.  there are multiple records with the identical employee name.  a 'distinct' can be done in the sql to get a single name instance.
2.  the 'emp' field is a text field and the 'hours' field is a number field.

In the query, I can use distinct to get a single employee name.  Then I can sum the hours field for a total.  However, when trying to divide the total hours by the total number of distinct employees, how can this be done?

Can you please paste the output of the above code.  I think it would really highlight where the problem is.

I suspect it will look like this :

GrandThourCnt = 10
Hours = 40
getEmps.emp = Smith


I am guessing that what you want is something like

GrandThourCnt = 10
Hours = 40
getEmps.emp = 5 (total number of distinct employees)


ALso please indicate what database you are using as the code to do it in SQL will vary from database to database.  I wuould guess Access from the way you have phrased things so far....

Yes, I am using Access.
I am looking for a average of hours requested/assigned.
This I hope could be done by finding how many employees have made a request (this is gotten by using 'distinct' for the emp field), then find the total number of hours requested (done by doing a sum of the hours), then dividing the two to get an average of hours requested.

I am looking to only display the average.

what does your table look like

Here is an example of data:

Emp                      INumber      ICodeNumber      Hours
Johnson, Jeff      383671      RMID0055                      0
Johnson, Jeff      384719      BKRD0943                      11
Johnson, Jeff      384693      PMCD3108                      25
Johnson, Jeff      384657      PMCD3043                      4

oops i mean if you like the coldfusion version.
if you like the SQL version, i think russmicheals suggestion will work on access.

The problem is that you are using the employee name as the divisor.

Jerry-Pang's solution will not work either becasuse it still uses the employee name.

You need to either use russmichaels  SQL answer or you need to change the Cold Fusion to :

<cfset average_emp=(#GrandThourCnt#+#hours#) / #getEmps.RecordCount#>

using SQL is faster and more efficient as you are getting the data directly from the database in the format you want, rather than getting the data form the database, passing it to coldfusion, converting it then displaying it.

Use your tools as they are intended. Don't get coldfusion to do the database work or vise versa, as they are obviously both better at doing the job for which they were intended.

Whenever you can convert, manipulate or extract the data from the database in the format your require without involving Coldfusion, do so.

Regards

Russ Michaels

It depends if the query is being used for something else as well.  In that case there is no reason not to have cold fusion do the calculation.

Any calculated columns can be returned in ALIAS columns leaving the original columns intact for other purposes. So there is also no need to do the calculations with coldfusion for that reason either.

example.

secect a, b, (a+b) AS c

you have performed a calcculation on columns a + b but returned the results in a new column called c, thus keeping the original data.

Just as you would not use your diskwasher to wash clothes. Yes the dishwasher can do it, but the washing machine is intended for washing clothes and will do it betetr and faster.

In the same way a database server is intended for working with data, returning result sets and manipulating that data. coldfusion can also do this but that is not what it is intended for.

i hope this makes sense for you mrichmon.

Russ

I know about calucalted and aliases columns.

But let's say that I wanted to display each record in the query.
SHould I create a calculated column associated with every record when the data does not really pertain to every record?
Then the calculated column result would look like this :

Emp                     INumber     ICodeNumber     Hours         AvgHours
Johnson, Jeff     383671     RMID0055                     0            10
Johnson, Jeff     384719     BKRD0943                     11           10
Smith, Mary     384693     PMCD3108                     25            10
Smith, Mary     384657     PMCD3043                     4               10

Which really doesn't make sense to store this data with every record.

Should I run a second query?

Or should I just have cold fusion do the calculation?

Probably the last one is the most efficeint.

In this user's case the SQL solution proposed by Meps is probably the best answer.

However in the situation I mentioned it would be a better solution to have CF do the calculation.

I brought it up so that the user would realize that there are many ways to acheive the same goal.


Cold fusion is intended to do calucaltions.  Yes it is more efficeint in a majority of cases to have SQL do the calculations, but not all.  So why use SQL in the cases where it is more efficeint in Cold Fusion?  Using your words "it would be better and faster" to have cold fusion do it in that case, than to have SQL do the calculation.

SO it really depends on the application and uses for which method is better.

Everyone had great ideas and input to this issue/question.
So, I am increasing the points and splitting them among all.  
I trust this will be welcomed, as the two major suggestions worked well.

I did however go with this one below.
Thanks to everyone for your time.

Cheers

<cfquery datasource="#dsn#" name="gethours">
      select sum(hours) as thours
      from c
</cfquery>

<cfoutput query="gethours">
<tr><td colspan="2" class="FieldNameTD3"><strong>GRAND TOTAL HOURS REQUESTED:&nbsp;&nbsp;&nbsp;<font color="blue">#thours#</font></strong></td>
<td colspan="2" class="FieldNameTD3"><strong>CURRENT NUMBER OF REQUEST THIS WEEK:&nbsp;&nbsp;&nbsp;<font color="blue">#getEmps.RecordCount#</font></strong></td>
<td colspan="2" class="FieldNameTD3"><strong>AVERAGE HOURS REQUESTED:&nbsp;&nbsp;&nbsp;
<cfset average_emp=#gethours.thours# / #getEmps.RecordCount#>
<font color="blue">#LEFT(average_emp,4)#</font></strong></td></tr>
</cfoutput>

The output looks like this:

GRAND TOTAL HOURS REQUESTED:   1014.0        CURRENT NUMBER OF REQUEST THIS WEEK:   25
AVERAGE HOURS REQUESTED:    40.5

mrichmon,
you do not actually have to output all the columns in a query. You can choose which columns to output.
If you wanted to use the same data multiple times, you can do so, you can easily ignore the calculated column, there is no need to run the same query twice, one with the calculated column, and one without. It is obviously faster to do 1 query and ignore the columns you do not need than to run 2 queries.

Russ