Replacing NULL with 0 in a SQL server query

Question

I have developed a query, and in the results for the first three columns I get NULL. How can I replace it with 0?

Select c.rundate, 
  sum(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded, 
  sum(case when c.runstatus = 'Failed' then 1 end) as Failed, 
  sum(case when c.runstatus = 'Cancelled' then 1 end) as Cancelled, 
  count(*) as Totalrun from
  (    Select a.name,case when b.run_status=0 Then 'Failed' when b.run_status=1 Then 'Succeeded'
  when b.run_status=2 Then 'Retry' Else 'Cancelled' End as Runstatus,
  ---cast(run_date as datetime)
              cast(substring(convert(varchar(8),run_date),1,4)+'/'+substring(convert(varchar(8),run_date),5,2)+'/'          +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate
  from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock) 
  on a.job_id=b.job_id
  where a.name='AI'
  and b.step_id=0) as c
  group by 
  c.rundate

@user2246674 The first three columns: sum(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded, sum(case when c.runstatus = 'Failed' then 1 end) as Failed, sum(case when c.runstatus = 'Cancelled' then 1 end) as Cancelled — Bhaskar Mishra
– Bhaskar Mishra, Commented May 30, 2013 at 15:51
Sparky, Oracle is different not isnull to use NVL or NVL2 ... check oracle-base.com/articles/misc/null-related-functions — KingRider
– KingRider, Commented Dec 5, 2016 at 12:31

phadaphunk · Accepted Answer · 2013-05-30 15:49:21Z

552

When you want to replace a possibly null column with something else, use IsNull.

SELECT ISNULL(myColumn, 0 ) FROM myTable

This will put a 0 in myColumn if it is null in the first place.

answered May 30, 2013 at 15:49

phadaphunk

13.4k17 gold badges77 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kyle Over a year ago

For those few using SQL Server 2000 or 2005 ISNULL is SQL Server 2008 and above.

flaudre Over a year ago

for multiple columns do I have to write ISNULL multiple times or is there something like ISNULL(myColumns, 0)?

Heinzi Over a year ago

@Kyle: That is incorrect: From personal experience (and a book quote), I can confirm that ISNULL is supported since (at least) SQL Server 2000, probably even earlier.

Heinzi Over a year ago

@Flaudre: You have to write ISNULL multiple times, since every output column must have its own expression.

lucky62 Over a year ago

SQLite equivalent function is IFNULL()

|

Mojtaba Rezaeian · Accepted Answer · 2014-11-14 06:42:32Z

You can use both of these methods but there are differences:

SELECT ISNULL(col1, 0 ) FROM table1
SELECT COALESCE(col1, 0 ) FROM table1

Comparing COALESCE() and ISNULL():

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

-- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL.

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0) PRIMARY KEY, 
    col3 AS ISNULL(col1, 0) 
);

-- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0), 
    col3 AS ISNULL(col1, 0) PRIMARY KEY 
);

Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.

if you need to know more here is the full document from msdn.

score 32 · Accepted Answer · 2013-05-30 15:54:52Z

32

With coalesce:

coalesce(column_name,0)

Although, where summing when condition then 1, you could just as easily change sum to count - eg:

count(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded,

(Count(null) returns 0, while sum(null) returns null.)

edited May 30, 2013 at 15:54

answered May 30, 2013 at 15:49

user359040

Comments

Rich Tolley · Accepted Answer · 2016-07-02 10:52:15Z

15

A Simple way is

UPDATE tbl_name SET fild_name = value WHERE fild_name IS NULL

edited Jul 2, 2016 at 10:52

Rich Tolley

3,8421 gold badge32 silver badges39 bronze badges

answered Jul 2, 2016 at 9:26

Umang Patwa

2,9654 gold badges35 silver badges41 bronze badges

Comments

GWed · Accepted Answer · 2019-10-29 14:27:45Z

14

If you are using Presto, AWS Athena etc, there is no ISNULL() function. Instead, use:

SELECT COALESCE(myColumn, 0 ) FROM myTable

answered Oct 29, 2019 at 14:27

GWed

15.8k7 gold badges65 silver badges105 bronze badges

Comments

sgeddes · Accepted Answer · 2013-05-30 15:49:44Z

13

When you say the first three columns, do you mean your SUM columns? If so, add ELSE 0 to your CASE statements. The SUM of a NULL value is NULL.

sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled,

SQL Fiddle Demo

answered May 30, 2013 at 15:49

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Comments

dKen · Accepted Answer · 2013-05-30 15:49:50Z

8

Use COALESCE, which returns the first not-null value e.g.

SELECT COALESCE(sum(case when c.runstatus = 'Succeeded' then 1 end), 0) as Succeeded

Will set Succeeded as 0 if it is returned as NULL.

answered May 30, 2013 at 15:49

dKen

3,1471 gold badge30 silver badges39 bronze badges

Comments

Stevoisiak · Accepted Answer · 2023-01-10 17:00:04Z

8

Wrap your column in this code.

ISNULL(Yourcolumn, 0)

Maybe check why you are getting nulls

edited Jan 10, 2023 at 17:00

Stevoisiak

27.8k32 gold badges140 silver badges245 bronze badges

answered May 30, 2013 at 15:50

Bobby

2,9383 gold badges22 silver badges37 bronze badges

Comments

Stevoisiak · Accepted Answer · 2023-01-10 16:58:42Z

Add an else to your case statements so that they default to zero if the test condition is not found. At the moment if the test condition isn't found NULL is being passed to the SUM() function.

Select c.rundate, 
  sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
  sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
  sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled, 
  count(*) as Totalrun from
  (    Select a.name,case when b.run_status=0 Then 'Failed' when b.run_status=1 Then 'Succeeded'
  when b.run_status=2 Then 'Retry' Else 'Cancelled' End as Runstatus,
  ---cast(run_date as datetime)
              cast(substring(convert(varchar(8),run_date),1,4)+'/'+substring(convert(varchar(8),run_date),5,2)+'/'          +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate
  from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock) 
  on a.job_id=b.job_id
  where a.name='AI'
  and b.step_id=0) as c
  group by 
  c.rundate

Krishna Chavali · Accepted Answer · 2016-09-23 14:08:41Z

0

sum(case when c.runstatus = 'Succeeded' then 1 else 0 end) as Succeeded, 
sum(case when c.runstatus = 'Failed' then 1 else 0 end) as Failed, 
sum(case when c.runstatus = 'Cancelled' then 1 else 0 end) as Cancelled,

the issue here is that without the else statement, you are bound to receive a Null when the run status isn't the stated status in the column description. Adding anything to Null will result in Null, and that is the issue with this query.

Good Luck!

answered Sep 23, 2016 at 14:08

Krishna Chavali

112 bronze badges

Comments

Chameleon · Accepted Answer · 2019-04-06 13:22:42Z

0

by following previous answers I was losing my column name in SQL server db however following this syntax helped me to retain the ColumnName as well

ISNULL(MyColumnName, 0) MyColumnName

answered Apr 6, 2019 at 13:22

Chameleon

1492 silver badges8 bronze badges

Comments

shieldgenerator7 · Accepted Answer · 2021-12-29 05:56:04Z

0

For regular SQL, ISNULL(item) can only take one parameter, and thus 90% of these solutions don't work.

I repurposed @Krishna Chavali's answer to make this:

(CASE WHEN (NOT ISNULL(column_name)) THEN column_name ELSE 0 END) AS ColumnName

This will return the value in column_name if it is not null, and 0 if it is null.

answered Dec 29, 2021 at 5:56

shieldgenerator7

1,7761 gold badge22 silver badges25 bronze badges

Comments

Donald Duck is with Ukraine · Accepted Answer · 2020-11-27 12:26:21Z

-6

UPDATE TableName SET ColumnName= ISNULL(ColumnName, 0 ) WHERE Id = 10

edited Nov 27, 2020 at 12:26

Donald Duck is with Ukraine

9,00223 gold badges82 silver badges106 bronze badges

answered Nov 27, 2020 at 11:20

Chandan Kumar

11 bronze badge

1 Comment

Donald Duck is with Ukraine Over a year ago

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.

Collectives™ on Stack Overflow

Replacing NULL with 0 in a SQL server query

13 Answers 13

6 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

13 Answers 13

6 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related