?
Solved

Getting SUM of column in a recordset.

Posted on 2003-03-19
13
Medium Priority
?
357 Views
Last Modified: 2008-03-04
I am trying to create a recordset that returns the total amount of hours and total amount of labor for a customer.
Here is my sql i am using to get the total labor and it works fine because the data type is 'money':

SELECT SUM(total_labor) AS grandtotallabor
FROM dbo.workorders

But when I try this...

SELECT SUM(total_hours) AS grandtotalhours
FROM dbo.workorders

I get this error: The sum or average aggregate operation cannot take a varchar data type as an argument.
I know that SUM only works with certain data types. I am using varchar because some hours are not whole numbers( example: 1.5 hours, .25 hours)and when I try to use numeric or decimal datatypes, I can't insert numbers that are decimals. Is there a workaround for this? Just need some way to add the "hours" column in the recordset.
0
Comment
Question by:strangedesign
[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
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 8168481
There are a few things here:

if your datatype is numeric, you should be able to insert decimals--you just need to set the precision

if you want to satay with the varchar, you can cast it as a number:

Select sum(Cdbl(total_hours)) as grandtotal hours

What database are you using?

Fritz the Blank
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 8168520
Also, if the data is a non-integer type, you can convert it to an integer using CInt(), this way if you had to do the math in the script you could do it in a loop;

Do While Not rs.EOF
  intTotalHrs = (intTotalHrs + rs("total_hours"))
Loop
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 8168524
>>What database are you using?<<
MS SQL Server

So this line:
>>Select sum(Cdbl(total_hours)) as grandtotal hours<<

Becomes:
Select sum(cast(total_hours as numeric(18,2))) as grandtotal hours<<

But Fritz is right you really need to find a way of defining it as numeric value, even if it means using minutes as opposed to hours.

Anthony
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8168532
And of course that should have been:
I suspect MS SQL Server

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 8168553
I am trying to remember the details, but once you set the type in the database as numeric, you should be able to specify the desired number of decimal points.

That really is the way to go, because this data conversion stuff becomes a real drag after awhile.

Fritz the Blank
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 8168567
"once you set the type in the database as numeric, you should be able to specify the desired number of decimal points", I agree.  
0
 
LVL 1

Author Comment

by:strangedesign
ID: 8168781
I am using MS SQL
0
 
LVL 1

Author Comment

by:strangedesign
ID: 8168829
That worked perfectly. Thank You!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8168866
fritz_the_blank,

Sorry about that, did not mean to "steal" your points.

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 8168915
Not at all, Anthony. I still think that strangedesign should use the numeric datatype in the database, but you corrected my faulty syntax for data casting.

Besides, after all of the times that you have helped me out, there is no way that I could feel that you were "stealing" from me anyway.

Fritz the Blank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8169125
>>I still think that strangedesign should use the numeric datatype in the database<< Absolutely.

Thanks,
Anthony
0
 

Expert Comment

by:mxs1964
ID: 11802826
How do I convert a number like 1106643 .56 to $1,106,643.56 to print on the screen and in reports by just using ASP?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11806719
Try posting a new question in this Topic Area.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

752 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