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

Question

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

User · Accepted Answer

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)

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.

User · Answer

ORACLE PLSQL    NVL FUNCTION  SELECT NVL SUM Price   0  AS TotalPrice  FROM Inventory WHERE  DateAdded BETWEEN  StartDate AND  EndDate    This SQL statement would return 0 if the SUM Price  returned a null value  Otherwise  it would return the SUM Price  value

User · Answer

The easiest way to do this is just to add zero to your result   i e    A   row  SUM Price   0   echo  A    hope this helps

User · Answer

You could use   SELECT ISNULL SUM ISNULL Price  0    0    I m 99  sure that will work

User · Answer

SELECT 0 COALESCE SUM Price  0  AS TotalPrice FROM Inventory WHERE  DateAdded BETWEEN  StartDate AND  EndDate

User · Answer

SELECT COALESCE       SELECT SUM Price  AS TotalPrice      FROM Inventory     WHERE  DateAdded BETWEEN  StartDate AND  EndDate         0    If the table has rows in the response it returns the SUM Price    If the SUM is NULL or there are no rows it will return 0   Putting COALESCE SUM Price   0  does NOT work in MSSQL if no rows are found

User · Answer

You can use ISNULL     SELECT ISNULL SUM Price   0  AS TotalPrice  FROM Inventory WHERE  DateAdded BETWEEN  StartDate AND  EndDate    That should do the trick

User · Answer

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

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

Examples related to sql

Examples related to tsql

Examples related to null

Examples related to sum