Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count of Count subquery

Status
Not open for further replies.

JustATheory

IS-IT--Management
Feb 27, 2003
115
US
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
 
Code:
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
 
Note that you don't need the State column in the inner query (though of course you keep the GROUP BY State part).
 
r937.com,

Thank you, it works like a charm

JustATheory
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top