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

Question

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

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

You can assume that all tables have been configured correctly.

If I make a choice between two dates and there are no elements in this date range, the function returns NULL as TotalPrice, not 0.

How can I make sure that if no records are found, 0 is returned, not NULL?

Answer 1

Most database servers have a COALESCE function that will return the first argument, which is not 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 the situation, since there seems to be a lot of discussion that COALESCE / ISNULL will still return NULL if the rows do not match, try this query, you can copy and paste into SQL Server directly as it is: / p>

 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 rows from sys.columns from consideration, but the sum statement still returns a single row, which is null, which combines the corrections as a single row with 0.

Hope this helps explain this.

Answer 2

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

That should do the trick.

Answer 3

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

Answer 4

Edit: Looks like everyone else beat me to 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 need to specify 0 instead.

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

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 SUM (Price). If SUM is NULL or no lines, it will return 0.

Entering COALESCE (SUM (Price), 0) DOES NOT work in MSSQL if no rows are found.

Answer 6

you can use

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

I am 99% sure that this will work.

Answer 7

ORACLE / PLSQL:

FUNCTION NVL

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

This SQL statement returns 0 if SUM(Price) returns null. Otherwise, it will return SUM(Price) .

Answer 8

The easiest way to do this is to simply add a zero to your result.

i.e.

 $A=($row['SUM'Price']+0); echo $A; 

hope this helps!