Count of counts - syntax

[mkrausnick]

I have a table that contains 1 or more records per member. I want to know how many members have only 1 record, how many have 2, how many have 3 records, etc. in the table.

In query analyzer, I want to do something like this:

select dups,count(*) as cnt from (
select memberid,count(*) as dups from mytable group by memberid) group by dups

Obviously this syntax didn't work. In the past I'd write code to throw the sub-query into an intermediate table and operate on that.

Is there way to accomplish this in Query Analyzer without using an intermediate table?

Mike Krausnick
Dublin, California

 

[gmmastros]

Subqueries require an alias.

select dups,count(*) as cnt from (
select memberid,count(*) as dups from mytable group by memberid) [!]As AnyOldAlias[/!] group by dups

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

I would do this with a derived table. I thnk you are close with wht you have but dervied tables always need an alias. Try:

select dups,count(*) as cnt from (
select memberid,count(*) as dups from mytable group by memberid) a 
group by dups

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mkrausnick]

That did it! Thanks for the quick response.

Mike Krausnick
Dublin, California