How can I change NULL to 0 when getting a single value from a SQL function?
SqlTsqlNullSumSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
SELECT 0+COALESCE(SUM(Price),0) AS TotalPrice
FROM Inventory
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
Solution 4 - Sql
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 - Sql
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.
Solution 6 - Sql
You could use
SELECT ISNULL(SUM(ISNULL(Price, 0)), 0)
.
I'm 99% sure that will work.
Solution 7 - Sql
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.
Solution 8 - Sql
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!!