How can I change NULL to 0 when getting a single value from a SQL function?

Question

Hi,

I have a query that counts the price of all items between two dates. Here is the select statement:

SELECT SUM(Price) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

You can assume all of the tables have been set up properly.

If I do a select between two dates and there are no items within that date range, the function returns NULL as the TotalPrice rather than 0.

How can I make sure that if no records are found, 0 gets returned rather than NULL?

Thanks,
Matt

Answer 1

Most database servers have a COALESCE function, which will return the first argument that is non-null, so the following should do what you want:

SELECT COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

[edit]

Just to clarify things since there seems to be a lot of discussion about "COALESCE/ISNULL will still return NULL if no rows match", try this query you can copy-and-paste into SQL Server directly as-is:

SELECT coalesce(SUM(column_id),0) AS TotalPrice 
FROM sys.columns
WHERE (object_id BETWEEN -1 AND -2)

Note that the where clause excludes all the rows from sys.columns from consideration, but the 'sum' operator still results in a single row being returned that is null, which coalesce fixes to be a single row with a 0.

I hope this helps explain it.

Answer 2

SELECT ISNULL(SUM(Price), 0) AS TotalPrice 
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

That should do the trick.

Answer 3

Edit: Looks like everyone else beat me to it haha

Found the answer.

ISNULL() determines what to do when you have a null value. In this case my function returns a null value so I needed specify a 0 to be returned instead.

SELECT ISNULL(SUM(Price), 0) AS TotalPrice FROM Inventory WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

Answer 4

SELECT 0+COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)

Answer 5

USE

SELECT ISNULL(SUM(ISNULL(Price, 0)), 0). I'm 99% sure that will work.