Solved

Getting SUM of column in a recordset.

Posted on 2003-03-19
Medium Priority
357 Views
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
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
• 5
• 3
• 2
• +2

LVL 46

Expert Comment

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

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

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

LVL 75

Expert Comment

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

Anthony
0

LVL 46

Expert Comment

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

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

ID: 8168781
I am using MS SQL
0

LVL 1

Author Comment

ID: 8168829
That worked perfectly. Thank You!
0

LVL 75

Expert Comment

ID: 8168866
fritz_the_blank,

Anthony
0

LVL 46

Expert Comment

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

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

Thanks,
Anthony
0

Expert Comment

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

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

Featured Post

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
Course of the Month11 days, 18 hours left to enroll