How can I change NULL to 0 when getting a single v

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?

Answer 1

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!!

Answer 2

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 3

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

That should do the trick.

Answer 4

You could use

SELECT ISNULL(SUM(ISNULL(Price, 0)), 0).

I'm 99% sure that will work.

Answer 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.

Answer 6

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.