Solved

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

Posted on 2004-08-09
17
223 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
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 50 total points
Comment Utility
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 50 total points
Comment Utility
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
Comment Utility
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
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
Comment Utility
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
Comment Utility
what does your table look like
0
 
LVL 1

Author Comment

by:g118481
Comment Utility
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 50 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 50 total points
Comment Utility
[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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now