Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

null

tsql

sum

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?

like image 283
Matt Avatar asked Jun 17 '09 03:06

Matt


People also ask

How do you replace NULL values to zero in SQL?

UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.

How do you replace NULL values in SQL?

The ISNULL Function is a built-in function to replace nulls with specified replacement values. To use this function, all you need to do is pass the column name in the first parameter and in the second parameter pass the value with which you want to replace the null value.

How do you replace NULL in SQL with text?

There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.

Is NULL equal to 0 in SQL?

In SQL Server, NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (' '). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as '=' or '<>'.


2 Answers

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.

like image 186
Jonathan Rupp Avatar answered Sep 27 '22 18:09

Jonathan Rupp


You can use ISNULL().

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

That should do the trick.

like image 42
Joseph Avatar answered Sep 27 '22 18:09

Joseph