Solved

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

Posted on 2004-08-09
17
226 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
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 50 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 50 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 50 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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