Group by query is not returning what I expected
sql, sql-server, sql-server-2005
Solution
select groupCol, count(distinct infoCol) as distinctInfoCol
from #t
where infoCol <> 0
group by groupCol
having count(distinct infoCol) > 1
Problem
I am attempting to find all records that have the same `groupCol` but more than one non 0 `infoCol`. Here is a complete example of what I am 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 from my query is ``` groupCol distinctInfoCol -------------- --------------- ExcludedZero 2 NoRows 3 TwoResults 2 ``` When I expected my output to be ``` groupCol distinctInfoCol -------------- --------------- TwoResults 2 ThreeResults 3 TwoAndZero 2 ``` What am I doing wrong, and how do I correct this to get the results I need?