Check for sum and null values

Question

Check for sum and null values

vigneshkumart50

Ten Centuries

Points: 1205
More actions
March 28, 2014 at 8:11 am

#287049

Hi,
select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]
from Products
How can I sum this column values and need to set a validation like the column has null values it has to return zero.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

joshdbguy SSCertifiable Points: 7707 More actions · Answer 1

vigneshkumart50 (3/28/2014)
Hi,
select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]
from Products
How can I sum this column values and need to set a validation like the column has null values it has to return zero.

If I understand you correctly, you can do something like this,

SELECT '$ ' + ISNULL(CONVERT(VARCHAR, CONVERT(DECIMAL(10, 0), CONVERT(MONEY, Amt_Value)), 1),0) AS [Amount]

FROM Products

samalex SSCertifiable Points: 5429 More actions · Answer 2

vigneshkumart50 (3/28/2014)
Hi,
select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]
from Products
How can I sum this column values and need to set a validation like the column has null values it has to return zero.

Use Coalesce which will return the first non-null value, then sum this to total the column:

DECLARE @Products TABLE (Amt_Value MONEY);

INSERT INTO @Products SELECT 123.44;

INSERT INTO @Products SELECT NULL;

INSERT INTO @Products SELECT 443.33;

SELECT'$ ' + CONVERT(VARCHAR,(CONVERT(DECIMAL(10,0),SUM(COALESCE(Amt_Value,0))))) as Amt

FROM @Products

-- Amt

-- $ 567