Creating a group by of a group by

I’m sure this is really straightforward, but I’m struggling! I’d like to create a group by for the count of the incidence of a value, eg. there are two incidences of case_id ’10’ and two of case_id ’20’, so there would be two counts of ‘two’

Data table:

id | case_id
---------------
0  | 10
1  | 10
2  | 20
3  | 20
4  | 30
5  | 30
6  | 30
7  | 40
8  | 40
7  | 40
8  | 40

Creates this:

no of occurrences  | count
of a case_id       | 
---------------------------
  2                |  2
  3                |  1
  4                |  1

Thank you!

Answer

Use an inner query:

SELECT occurences, COUNT(*) cnt
FROM (
    SELECT COUNT(*) occurences FROM mytable GROUP BY case_id
) x 
GROUP BY occurences

Demo on DB Fiddle:

| occurences | cnt |
| ---------- | --- |
| 2          | 2   |
| 3          | 1   |
| 4          | 1   |