Group by question - counting records within a group

[LucieLastic]

hi All

I thought this would be straight forward but can't get it to give me the right results. I have an extract of a simple table as follows

ID TypeID
1 2
1 2
1 3
1 2
2 1
2 1
2 1

I;d like the following results. It gives me a distinct count of the TypeIDs within each ID where there is >1 different TypeID, so it would look like this:

ID Count
1 2

I tried this query initially and lots of connotations of it but without success:

select ID, count(TypeID) from TypeList
group by ID, TypeID
having count(TypeID) > 1
order by ID

Where am I going wrong?

Thank you in advance for help.

Lou

 

[bborissov]

SELECT Id,
       COUNT(DISTINCT TypeId) AS DistCnt
FROM TypeList
GROUP BY Id
HAVING COUNT(DISTINCT TypeId) > 1
ORDER BY Id

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LucieLastic]

That is brilliant! Thank you! I never would have got that. I just can't understand why I don't have to include the TypeID in the group by, is there an easy explanation for that?

Thank you so much :O)

 

[bborissov]

Because that field is counted.
If you have TypeId in GROUP BY you ALWAYS will have DISTINCT COUNT equal to 1.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LucieLastic]

Ah, I understand, thank you again.