CODEWITHSUNDEEP
sqlsql-server
isha arora
isha arora

Reputation: 51

When count is giving blank with group by how to replace it with 0 in sql

below is the code which i am trying but its is giving blank . I need to insert this result as a row in another table, as it is giving blank due to which i am not able insert it into another table . Can someone please suggest how to replace that count result '' with 0

Select case when (count(ID) is Null ) OR   (count( ID) =''  )
    
    then  0 else cast((count( ID)) as varchar(50)) end as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1

Upvotes: 0

Views: 822

Answers (3)

s.ch
s.ch

Reputation: 144

You can use something like this:

Select case when (ISNULL(count(ID),0)=0)
       then 0 else cast((count( ID)) as varchar(50))
       end as Total 
from Temp1 
where quarter in ('202203','202204') 
group by ID, Quarter
having count(ID) >1

Upvotes: 1

Malvik Bhavsar
Malvik Bhavsar

Reputation: 440

Please refer to this link: Referance

You need to add union and sum of your Id.

Check the following way.

    select sum(Total) as Total from (
    Select case when (count(ID) is Null ) OR   (count( ID) =''  )
    
    then  0 else cast((count( ID)) as varchar(50)) end as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1 
           union all
           select 0 as Total
           ) report

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

If this does not do anything you look at the group by part first. Group by eliminates all rows and then there is no count in the group by.

to counter this condition use not exists like below

if not exists (Select  cast((count( ID)) as varchar(50)) as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1  )  
begin
insert into #result(total) select 0 as total
end
else
begin
insert into #result(total)
Select  cast((count( ID)) as varchar(50)) as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1
end

See demo below where I created an empty table

enter image description here

Upvotes: 0

Related Questions