?
Solved

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

Posted on 2004-08-09
17
Medium Priority
?
230 Views
Last Modified: 2013-12-24
Hi,

I have a CF web app the allocates hours to employees.  The data is written to an access table.
I also have a display page with the hours data that displays all the week's records that have been requested.
This page has the employee their hours and the total hours allocated.

I want to divide the total hours by the total number of employees.  Both numbers are dynamic, as it is not the same amounts every week.  The total hours is a numeric value in the table and the employee field is a text value.

When I wrote this, it gave me an error that division was not possible between data types.
How can I get what I need?

<cfset average_emp=(#GrandThourCnt#+#hours#) / #getEmps.emp#>
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 200 total points
ID: 11755699
It should work automatically.

Try this first before the above line :

<cfoutput>
GrandThourCnt = #GrandThourCnt#<br>
Hours = #hours#<br>
getEmps.emp = #getEmps.emp#
</cfoutput>
<cfabort>
<cfset average_emp=(#GrandThourCnt#+#hours#) / #getEmps.emp#>


Tell us the output.
0
 
LVL 3

Assisted Solution

by:Meps
Meps earned 200 total points
ID: 11755717
Could you use SQL to do this for you?

Select AVG(hours), employee
from emp
group by employee

but <cfset average_emp=(#GrandThourCnt#+#hours#) / #getEmps.emp#>
I am confused why you would be using getEmps.emp, normally emp stands for employee, is this field numeric?
0
 
LVL 1

Author Comment

by:g118481
ID: 11756580
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?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 35

Expert Comment

by:mrichmon
ID: 11756641
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....
0
 
LVL 1

Author Comment

by:g118481
ID: 11756726
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11756794
what does your table look like
0
 
LVL 1

Author Comment

by:g118481
ID: 11756833
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
0
 
LVL 2

Assisted Solution

by:russmichaels
russmichaels earned 200 total points
ID: 11757693
to divide total hours by number of employees would be done in SQL as follows.

select sum(Hours)/count(distinct Emp) AS AvgHours

This works on SQL server, not sure about Access though.

Regards

Russ Michaels
0
 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 200 total points
ID: 11761158
[quote]
When I wrote this, it gave me an error that division was not possible between data types.
How can I get what I need?

<cfset average_emp=(#GrandThourCnt#+#hours#) / #getEmps.emp#>
[/quote]


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

if the conversion in VAL() is impossible, 0 is returned

well, if you like the SQL version.

regards,
jerry
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 11761178
oops i mean if you like the coldfusion version.
if you like the SQL version, i think russmicheals suggestion will work on access.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11763740
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#>
0
 
LVL 2

Expert Comment

by:russmichaels
ID: 11765521
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
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11765892
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.
0
 
LVL 2

Expert Comment

by:russmichaels
ID: 11766062
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
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11767161
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.
0
 
LVL 1

Author Comment

by:g118481
ID: 11778573
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
0
 
LVL 2

Expert Comment

by:russmichaels
ID: 11781880
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
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question