Replacing Null with 0 in a SQL Server Query

Replacing NULL with 0 in a SQL server query

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.

replace NULL with Blank value or Zero in sql server

You can use the COALESCE function to automatically return null values as 0. Syntax is as shown below:

SELECT COALESCE(total_amount, 0) from #Temp1

Replace null with zero in sql query

This query:

SELECT (select count(goal) from fixtures where goal='1' and fixture='$fixture') as goalCountHome
FROM fixtures 
WHERE fixture = '$fixture'
LIMIT 1

cannot return NULL values. The subquery is an aggregation query with no GROUP BY, so it always returns one row. That row will contain a result from COUNT(). COUNT() itself can never return a NULL value. If there are no rows, then the value will be zero.

The outer query might return no rows but that is different from NULL values.

Of course, this query is way overcomplicated, and should simply be:

SELECT COUNT(*) as goalCountHome
FROM fixtures 
WHERE fixture = ? AND   -- pass this in as a parameter 
      goal = 1 ;        -- it looks like a number so I assume it is

Note that you should be passing parameters in using proper parameters rather than munging query strings.

how to Replace null with zero in pivot SQL query

create table tblInvoice (InvDt date, TenantId int, InvAmnt numeric)
insert into tblInvoice values ('20180601',1,1),('20180601',1,1),('20180601',1,1),('20180501',1,1), ('20180401',1,1)

SELECT Year
     , COALESCE(Jan   ,0) Jan
     , COALESCE(Feb   ,0) Feb
     , COALESCE(Mar   ,0) Mar
     , COALESCE(Apr   ,0) Apr
     , COALESCE(May   ,0) May
     , COALESCE(Jun   ,0) Jun
     , COALESCE(Jul   ,0) Jul
     , COALESCE(Aug   ,0) Aug
     , COALESCE(Sep   ,0) Sep
     , COALESCE(Oct   ,0) Oct
     , COALESCE(Nov   ,0) Nov
     , COALESCE(Dec   ,0) Dec
  FROM (   SELECT YEAR(InvDt)                     AS Year
                , LEFT(DATENAME(MONTH, InvDt), 3) AS Month
                , InvAmnt                         AS Amount
             FROM tblInvoice
            WHERE TenantId = 1) AS Inv
  PIVOT (   SUM(Amount)
            FOR Month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)) AS pvt;

How to Replace NULL Value with 0 (Zero)?

Just use conditional aggregation:

SELECT SUM(CASE WHEN Domain_Id = 1 THEN Fix_Count ELSE 0 END) as d_1,
       SUM(CASE WHEN Domain_Id = 2 THEN Fix_Count ELSE 0 END) as d_2,
       SUM(CASE WHEN Domain_Id = 3 THEN Fix_Count ELSE 0 END) as d_3,
       SUM(CASE WHEN Domain_Id = 4 THEN Fix_Count ELSE 0 END) as d_4,
       SUM(CASE WHEN Domain_Id = 5 THEN Fix_Count ELSE 0 END) as d_5       
FROM (SELECT lg.domainNameID AS Domain_ID, COUNT(*) AS Fix_Count
      FROM tbl_ATT_Request r JOIN
           tbl_ATT_Login lg
           ON lg.workdayID = r.workdayID
      WHERE r.requestCategoryID = 1
      GROUP BY lg.domainNameID
     ) d

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

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.

SQL replace zero 0 values with NULL or text

Use case expression for this:

SELECT Productname ,
       CASE WHEN ProductPrice = 0 THEN NULL
            ELSE ProductPrice
       END as ProductPrice
  FROM table

Replace NULL Values in a column with Zeros on a condition

You can use updatable CTEs and window functions:

with toupdate as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
update toupdate
    set current = 0
    where current is null and (prev_current is not null or next_current is not null);

If you just want an additional column in a select query (as opposed to changing the data), then:

with t as (
      select t.*, lag(current) over (order by datetime) as prev_current,
             lead(current) over (order by datetime) as next_current
      from t
     ) 
select t.*,
       (case when current is null and (prev_current is not null or next_current is not null)
             then 0 else current
        end) as new_current
from t;