# 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
###### Who is Participating?

Commented:
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

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

Commented:
Try this:

SELECT SUM(CAST(YourColumn AS INT)) AS YourColumn
FROM YourTable
0

Commented:
are there numbers in this column?
or do you want a concatinated string?
0

Software 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

Commented:
SELECT SUM(CAST(YourColumn AS DECIMAL(10, 2))) AS YourColumn
FROM YourTable
0

Software 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

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

SELECT *
FROM mMTownStock
WHERE ISNUMERIC(Cost) = 0 OR
ISNUMERIC(Quantity) = 0
0

Commented:
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

Software EngineerAuthor Commented:
it came back with no records
0

Commented:
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

Software EngineerAuthor Commented:
same thing
0

Software 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.