Janus Bahs Jacquet Janus Bahs Jacquet - 3 months ago 20
MySQL Question

(MySQL) Group by field and select both COUNT(field) and number of grouped rows

I have a many-to-many table with approximately this structure:

id | obj
----+---------
1 | 27
1 | 42
2 | 32
2 | 42
2 | 162
2 | 89
3 | 2
3 | 209


Essentially the table associates any number of objects (
obj
) with any number of collections (
id
).

I am trying to
SELECT
from this table in a way that will return the number of rows in a
GROUP BY
clause grouping by
id
, and also the number of grouped rows grouped by the number of rows in each group.

If I simply do
SELECT COUNT(id) FROM table GROUP BY id
, I naturally get the following:

id | COUNT(id)
----+---------
1 | 2
2 | 4
3 | 2


That is, there is one row where
COUNT(id) = 4
and there are two rows where
COUNT(id) = 2
. So far so good. But not what I’m looking for here.

What I need is this: for each distinct value returned by
COUNT(id)
(2 and 4 in this case), select both
COUNT(id)
and the number of rows that match that value in their
COUNT(id)
column (in this case 2 and 1, respectively: 2 rows have
COUNT(id) = 2
and 1 row has
COUNT(id) = 4
).

In other words, from the table above, I would want this:

id_cnt | grp_cnt
-------+---------
2 | 2
4 | 1


– since grouping the table by
id
, you get two rows where
COUNT(id)
is 2 (
id
s 1 and 3); and one row where
COUNT(id)
is 4 (
id
2).

Wrack my brain as I may, though, I cannot figure out a way to do this in one single query.

The closest I’ve been able to get to something that made sense in my head was this:

SELECT COUNT(*), id_cnt FROM table JOIN (SELECT COUNT(id) id_cnt FROM table GROUP BY id) a


– but that gives:

count(*) | id_cnt
---------+---------
21100 | 2


– which I admit confuses me a bit.

Can it be done?

 

(I find it odd that I can’t find this question already asked—surely this must have been asked before? Perhaps I’m just wording my search queries poorly…)

Answer

You can add another level of grouping around your first grouped query.

SELECT id_cnt, COUNT(*) AS grp_cnt 
FROM (
    SELECT COUNT(*) AS id_cnt 
    FROM test.test GROUP BY id) id_cnts
GROUP BY id_cnt;