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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Wrap your column in this code    ISNULL Yourcolumn  0    Maybe check why you are getting nulls

User · Answer

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

User · Answer

UPDATE TableName SET ColumnName  ISNULL ColumnName  0   WHERE Id   10

User · Answer

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

User · Answer

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

User · Answer

A Simple way is  UPDATE tbl name SET fild name   value WHERE fild name IS NULL

User · Answer

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

User · Answer

If you are using Presto  AWS Athena etc  there is no ISNULL   function  Instead  use   SELECT COALESCE myColumn  0   FROM myTable

[sql] Replacing NULL with 0 in a SQL server query

Examples related to sql

Examples related to sql-server