How can I change NULL to 0 when getting a single value from a SQL function?
Solution 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)
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.
Solution 2
You can use ISNULL()
.
SELECT ISNULL(SUM(Price), 0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
That should do the trick.
Solution 3
SELECT 0+COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
Solution 4
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)
Solution 5
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.
Matt
Updated on February 12, 2020Comments
-
Matt over 4 years
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?
-
Tetraneutron about 15 yearsIf you read the question he wants 0 to be returned if there were no results, this will only work if the result of the sum is null
-
hansvb about 15 years@Tetraneutron: I think it will work. sum(price) will be null if there are no rows, but there will always be exactly one result row.
-
ojrac about 15 yearsI believe IFNULL is the MySQL equivalent, and ISNULL is for MS' T-SQL.
-
Coryza about 15 yearsCOALESCE works fine so long as you're getting a row back for it to operate on (which you do in this case). COALESCE won't help you if you get no rows back though.
-
Coryza about 15 yearsI guess I should add that I only tried this on SQL Server 2008 and MySQL 5-- I don't have any other DBs available, but I'm pretty certain that isnull/coalesce alone will do it on any DB.
-
Tetraneutron about 15 yearsYou don't have a row to operate on, the question states "no records are found", so no row to operate on, so coalesce won't work.
-
Tetraneutron about 15 yearsActually yes you are right, I copied your answer across to test it, but in the process of fitting it to a temp table must have dropped the "Sum" off resulting in no rows returned, My apologies with an upvote.
-
dkretz about 15 yearsSee my answer below. Just use ISNULL (or COALESCE) twice, once for each row, then for the sum. SELECT ISNULL(SUM(ISNULL(Price, 0)), 0))
-
user3001801 about 15 yearsYou have the ISNULL statment backwards for what you are trying to do. I think you want this instead: SUM(ISNULL(Price,0))
-
Paul over 12 yearsOf course this prevents you from getting more than one value at a time from the Inventory table.
-
sayap almost 12 yearsThe inner ISNULL is not needed, as SUM will just ignore NULL values.
-
QMaster over 10 yearsGood answer but don't forget a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. so I think is better to use ISNULL function. for more information you could get: msdn.microsoft.com/en-us/library/ms190349.aspx
-
Vesper over 9 yearsThis might work if the topic starter has a PHP code to interpret the results, but if the question is limited to SQL, this won't do as an answer.
-
TylerH over 4 yearsNote that, while more efficient,
isnull()
only accepts 1 input vs any amount forcoalesce()
, andisnull()
is proprietary to T-SQL (thus, not portable).