Replacing NULL with 0 in a SQL server query

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

When you want to replace a possibly `null` column with something else, use [IsNull](

[To see links please register here]

).

SELECT ISNULL(myColumn, 0 ) FROM myTable

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

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][1]



[1]:

[To see links please register here]

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

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

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():**

1. The ISNULL function and the COALESCE expression have a similar
purpose but can behave differently.

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

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

4. 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
);


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

6. ISNULL takes only 2 parameters whereas COALESCE takes a variable
number of parameters.



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


[1]:

[To see links please register here]

***A Simple way is***

UPDATE tbl_name SET fild_name = value WHERE fild_name 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,

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!

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

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

SELECT COALESCE(myColumn, 0 ) FROM myTable