Solved

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

Posted on 2004-08-09
17
224 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
 
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
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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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