Getting SUM of column in a recordset.
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.
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.
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
Do While Not rs.EOF
intTotalHrs = (intTotalHrs + rs("total_hours"))
Loop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And of course that should have been:
I suspect MS SQL Server
Anthony
I suspect MS SQL Server
Anthony
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
That really is the way to go, because this data conversion stuff becomes a real drag after awhile.
Fritz the Blank
"once you set the type in the database as numeric, you should be able to specify the desired number of decimal points", I agree.
ASKER
I am using MS SQL
ASKER
That worked perfectly. Thank You!
fritz_the_blank,
Sorry about that, did not mean to "steal" your points.
Anthony
Sorry about that, did not mean to "steal" your points.
Anthony
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
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
>>I still think that strangedesign should use the numeric datatype in the database<< Absolutely.
Thanks,
Anthony
Thanks,
Anthony
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?
Try posting a new question in this Topic Area.
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