Search code examples
sqlsql-servernullcase

Return NULL instead of 0 when using COUNT(column) SQL Server

I have query which running fine and its doing two types of work, COUNT and SUM.

Something like 

select 
    id,
    Count (contracts) as countcontracts,
    count(something1),
    count(something1),
    count(something1),
    sum(cost) as sumCost
from 
    table
group by 
    id

My problem is: if there is no contract for a given ID, it will return 0 for COUNT and Null for SUM. I want to see null instead of 0

I was thinking about case when Count (contracts) = 0 then null else Count (contracts) end but I don't want to do it this way because I have more than 12 count positions in query and its prepossessing big amount of records so I think it may slow down query performance.

Is there any other ways to replace 0 with NULL?

Solution

  • Try this:

    select NULLIF ( Count(something) , 0)