Janus Bahs Jacquet - 11 months ago 57
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.

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…)

SELECT id_cnt, COUNT(*) AS grp_cnt