CODEWITHSUNDEEP
sql-serverisnull
Alex
Alex

Reputation: 3968

SQL Server ISNULL not working

I have the following query for SQL server:

SELECT SUM(ISNULL(CurrentVendorLastPurchCost, 0)) AS TheSum FROM VW_Report_AvailableInventory WITH(NOLOCK)

The table that I am running it on is empty. Given the ISNULL clause, I would expect it to return 0.

However, it returns NULL.

I have had a look at a number of posts and tried various different combinations of code, but none of them produce the result I want.

What am I doing wrong??

Upvotes: 0

Views: 3452

Answers (2)

Fred
Fred

Reputation: 5808

You don't need the ISNULL inside SUM. Wrap it round the sum instead. Try. 

SELECT ISNULL(SUM(CurrentVendorLastPurchCost), 0) AS TheSum FROM VW_Report_AvailableInventory WITH(NOLOCK)

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

As your table is empty then you haven't any value in there to apply ISNULL function and the result of SUM on empty set is NULL. You shouldn't care about null values in summing. To solve your issue use:

SELECT ISNULL(SUM(CurrentVendorLastPurchCost), 0) AS TheSum
FROM VW_Report_AvailableInventory WITH(NOLOCK)

Upvotes: 2

Related Questions