The request group does not return what I expected

Question

I am trying to find all records that have the same groupCol , but more than one, not equal to 0 infoCol . Here is a complete example of what I'm trying to do.

 CREATE TABLE #t ( groupCol varchar(14) NOT NULL, infoCol int NOT NULL, indexCol int NOT NULL IDENTITY (1, 1) ) GO insert into #t (groupCol, infoCol) values ('NoRows',1), ('NoRows',1), ('NoRows',1), --Not in output due to having only 1 disticnt infoCol ('TwoResults',1), ('TwoResults',1), ('TwoResults',2), --In the output with "'TwoResults', 2" ('ThreeResults',1),('ThreeResults',2),('ThreeResults',3), --In the output with "'ThreeResults', 3" ('ExcludedZero',1),('ExcludedZero',1),('ExcludedZero',0) --Not in the output due to 0 being excluded for finding distinct infoCol's ('TwoAndZero',1), ('TwoAndZero',2), ('TwoAndZero',0) --In the output but returns 2 not 3. select * from #t select groupCol, count(groupCol) as distinctInfoCol from #t where infoCol <> 0 group by groupCol, infoCol having count(groupCol) > 1 drop table #t 

However, the result of my query is

  groupCol distinctInfoCol
 -------------- ---------------
 ExcludedZero 2
 NoRows 3
 TwoResults 2

When I expected my conclusion would be

  groupCol distinctInfoCol
 -------------- ---------------
 TwoResults 2
 ThreeResults 3
 TwoAndZero 2

What am I doing wrong, and how can I fix this to get the right results?

Answer 1

 select groupCol, count(distinct infoCol) as distinctInfoCol from #t where infoCol <> 0 group by groupCol having count(distinct infoCol) > 1