getting a sum

How do I get a total of one column? The only thing I want displayed is the total of all data from 1 column. Also, the column is varchar. TIA
Mike MillerSoftware EngineerAsked:
Who is Participating?
 
rafranciscoCommented:
Try changing the DECIMAL(10, 2) to float first and see if the error will go away.  If the error goes away, take note of the number of digits of the output and maybe you have to increase the DECIMAL(10,2) to whatever the result will be, like DECIMAL(18,2):

SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS FLOAT)) AS NetTotal
FROM (cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS FLOAT)) AS NetTotal
0
 
TimCotteeHead of Software ServicesCommented:
Hi mwmiller78,

Well as long as all the data in the column is numeric you can do

Select Sum(Cast MyField As Integer) As Total From MyTable

Again, if you have decimals there you need to change the cast

Tim Cottee
0
 
rafranciscoCommented:
Try this:

SELECT SUM(CAST(YourColumn AS INT)) AS YourColumn
FROM YourTable
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lluthienCommented:
are there numbers in this column?
or do you want a concatinated string?
0
 
Mike MillerSoftware EngineerAuthor Commented:
sorry, yes it's all numbers. there are decimals also. i suppose i would need to cut off everything after the second digit to the right of the decimal as well.
0
 
rafranciscoCommented:
SELECT SUM(CAST(YourColumn AS DECIMAL(10, 2))) AS YourColumn
FROM YourTable
0
 
Mike MillerSoftware EngineerAuthor Commented:
Here's what I'm trying...
SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS DECIMAL(10, 2))) AS NetTotal
FROM (cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS DECIMAL(10, 2))) AS NetTotal

I'm getting aritmatic overflow converting datatype to numeric

0
 
rafranciscoCommented:
Try this to identify the row that's causing the problem

SELECT *
FROM mMTownStock
WHERE ISNUMERIC(Cost) = 0 OR
           ISNUMERIC(Quantity) = 0
0
 
rafranciscoCommented:
SELECT *
FROM mMTownStock
WHERE (ISNUMERIC(Cost) = 0 AND Cost IS NOT NULL AND Cost != '') OR
            (ISNUMERIC(Quantity) = 0 AND Quantity IS NOT NULL AND Quantity != '')
0
 
Mike MillerSoftware EngineerAuthor Commented:
it came back with no records
0
 
rafranciscoCommented:
Do the same on the Main table:

SELECT *
FROM Main
WHERE (ISNUMERIC(Cost) = 0 AND Cost IS NOT NULL AND Cost != '') OR
            (ISNUMERIC(Quantity) = 0 AND Quantity IS NOT NULL AND Quantity != '')
0
 
Mike MillerSoftware EngineerAuthor Commented:
same thing
0
 
Mike MillerSoftware EngineerAuthor Commented:
thats what it was!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.