CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Scott Chamberlain
Scott Chamberlain

Reputation: 127543

Group by query is not returning what I expected

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?

Upvotes: 4

Views: 53

Answers (1)

Ender Wiggin
Ender Wiggin

Reputation: 424

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

Upvotes: 4

Related Questions