?
Solved

Getting SUM of column in a recordset.

Posted on 2003-03-19
13
Medium Priority
?
359 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

615 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