Count of Count subquery

[JustATheory]

Hi,

I need to do a count of a count field in one query, it's just not happening. Basic idea:

Table

Account State
123 CA
456 CA
789 HI
122 WA

Selct State, count(state)CNT from Table
Group by STATE;

Result:

State CNT
CA 2
HI 1
WA 1

What I need to include as a subquery is counting the CNT so that the results are:

CNT CountOfCNT
2 1
1 2

I keep receiving errors, and it seems that this would be an easy subquery, what is the next step?

Any help is welcomed!

JustATheory

 

[r937]

SELECT cnt
     , COUNT(*) AS CountOfCNT
  FROM ( SELECT State
              , COUNT(*) AS cnt
           FROM daTable
         GROUP
             BY State ) AS s
GROUP
    BY cnt

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Emtucifor]

Note that you don't need the State column in the inner query (though of course you keep the GROUP BY State part).

 

[r937]

yup, nice catch

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JustATheory]

r937.com,

Thank you, it works like a charm

JustATheory