CODEWITHSUNDEEP
sqlsqlite
Hexalyse
Hexalyse

Reputation: 368

SQLite: Count the number of similar COUNT(*) rows resulting from a GROUP_BY statement

I'm not very good at SQL, and I have a very peculiar request to do.

My table looks something like this :

FOO  BAR  
----+----
foo1 bar1
foo2 bar3
foo1 bar1
foo1 bar1
foo2 bar3 
foo4 bar3 
foo3 bar2 
foo2 bar4 
foo5 bar4

I manage easily to count the number of each different "bar" entries with a

SELECT bar, COUNT(*) as barcount FROM table GROUP BY bar ORDER BY barcount

which gives me

BAR  barcount
----+----
bar1  3
bar2  1
bar3  3
bar4  2

but what I'm trying to achieve is have a table where I know how many "bars" have a barcount of 1, how many have a barcount of 2 times, how many have a barcount of 3etc.

The restult I need is this, to make it simple:

barcount occurences
--------+-----------
  1          1
  2          1
  3          2

Is it possible to do this in a single SQL query, or would I have to rely on some code ?

Upvotes: 1

Views: 51

Answers (3)

dnoeth
dnoeth

Reputation: 60513

If you need to nest aggregates you must use a Derived Table (or Common Table Expression):

select barcount, count(*) as occurrences
from
 (
   SELECT bar, COUNT(*) as barcount 
   FROM table 
   GROUP BY bar 
 ) as dt
group by barcount
ORDER BY barcount

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

You could wrap your query in another query:

SELECT   barcount, COUNT(*) AS occurences
FROM     (SELECT   bar, COUNT(*) as barcount 
          FROM     mytable 
          GROUP BY bar) t
GROUP BY barcount
ORDER BY barcount

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select barcount, count(*) as occurences
from
(
  SELECT bar, COUNT(*) as barcount 
  FROM your_table 
  GROUP BY bar
) tmp
group by barcount

Upvotes: 2

Related Questions