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

Question

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

Your query cannot give a null count... there is no need to "replace" anything. — Salman A, Oct 06 '21 at 08:26
Does this answer your question? [Count Returning blank instead of 0](https://stackoverflow.com/questions/19221630/count-returning-blank-instead-of-0) — DhruvJoshi, Oct 06 '21 at 08:41
`its giving blank` because you have a `CASE` expression that convert it to string — Squirrel, Oct 06 '21 at 08:42
empty result set does not means `COUNT()` return a `NULL` value — Squirrel, Oct 06 '21 at 08:44
*"group by forces an empty set"* `GROUP BY` can't do this either. — Thom A, Oct 06 '21 at 08:59
@Larnu please run `select count(1) from sys.tables where 1<>1 group by type ` — DhruvJoshi, Oct 06 '21 at 09:09
The `GROUP BY` has nothing to do with filtering the data, @DhruvJoshi , that's the `WHERE`... There is **never** an event where `1` isn't equal to `1`... — Thom A, Oct 06 '21 at 09:13
@larnu have a look at the screenshot in my answer. create a table, delete all rows, run the count(1) with group by (w/o where). same result — DhruvJoshi, Oct 06 '21 at 09:23
@Larnu `create table temptable (id int) insert into temptable values (1),(2); delete from temptable select count(1) from temptable group by id ` — DhruvJoshi, Oct 06 '21 at 09:28
That's still not filtering, @DhruvJoshi ... The `WHERE` returns no row, but because you have another column, which has no rows, no rows are returned. The `GROUP BY` isn't forcing the empty result set, the `WHERE` is. If you removed the `GROUP BY` (and `COUNT`) you would *still* get an empty result set. — Thom A, Oct 06 '21 at 09:39
@Larnu still suggest you to have a look at the screenshot in answer or run `create table t (i int); select count(1) from t; select count(1) from t group by i` on your sql server — DhruvJoshi, Oct 06 '21 at 10:19
@Larnu Sure "group by forces an empty set" is true, eg: `GROUP BY ()` returns an empty set, see https://www.sql.kiwi/2012/03/fun-with-aggregates.html — Charlieface, Oct 06 '21 at 10:32

score 1 · Answer 1 · answered Oct 06 '21 at 08:49

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

s.ch · Answer 2 · 2021-10-06T12:18:36.390

1

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

edited Oct 06 '21 at 12:18

answered Oct 06 '21 at 10:04

s.ch

134
5

score 0 · Accepted Answer · answered Oct 06 '21 at 08:40

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

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

3 Answers3