getting a sum

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

Try this:

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

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

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.

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

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

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

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

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 != '')

it came back with no records

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 != '')

same thing

thats what it was!