How to Replace NULL Values with Zero in SQL Server Queries?

Replacing NULL Values with Zero in SQL Server Query

In a SQL Server query, you may encounter situations where you have NULL values in your results. Replacing these NULL values with a more meaningful representation, such as 0, can be necessary to ensure accurate data analysis.

To achieve this, you can utilize the ISNULL() function. The ISNULL() function takes two parameters: the expression you want to check for NULL values and the value you want to replace the NULL values with.

Query Syntax:

SELECT ISNULL(expression, 0) FROM table

Example:

In your provided query, you have three columns that may contain NULL values: Succeeded, Failed, and Cancelled. To replace these NULL values with 0, you can use the ISNULL() function as follows:

Select
  c.rundate,
  ISNULL(
    sum(case when c.runstatus = 'Succeeded' then 1 end),
    0
  ) as Succeeded,
  ISNULL(
    sum(case when c.runstatus = 'Failed' then 1 end),
    0
  ) as Failed,
  ISNULL(
    sum(case when c.runstatus = 'Cancelled' then 1 end),
    0
  ) 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(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

By using ISNULL(), you can ensure that the results for Succeeded, Failed, and Cancelled will always be a numerical value, even if there were NULL values in the original query results.

The above is the detailed content of How to Replace NULL Values with Zero in SQL Server Queries?. For more information, please follow other related articles on the PHP Chinese website!