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

Question

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

@Michael Yes, exactly. You get three rows, of which one has `COUNT(id) = 4` and two have `COUNT(id) = 2`. What I am trying to do is, for each value of `COUNT(id)`, return the number of rows that have that value. — Janus Bahs Jacquet, Aug 31 '16 at 17:59
@Michael The SQL has changed about 50 times in the past 30 minutes, most permutations resulting in various syntax errors—that’s part of the trouble. I’ve now updated the question to explain in more detail. — Janus Bahs Jacquet, Aug 31 '16 at 18:13
Maybe `SELECT id, COUNT(id) as idcnt ,COUNT(*) as rowcnt FROM table GROUP BY id` — JOUM, Aug 31 '16 at 18:15

score 3 · Accepted Answer · answered Aug 31 '16 at 18:09

3

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;

answered Aug 31 '16 at 18:09

Don't Panic

41,125
10
61
80

Brilliant! My brain is too frazzled right now to even properly understand why my very similar attempt didn’t work (I joined the subquery table instead of selecting directly from it… so that somehow [insert MySQL maths/magic here] inflated the number of rows in the result set quite ludicrously). But this works and does exactly what it’s supposed to. Many thanks! You and scaisEdge posted nearly identical answers, but you beat scaisEdge by 35 seconds, so I’ll award you the tick. :-) – Janus Bahs Jacquet Aug 31 '16 at 18:22
1

@Michael It didn't seem mad to me. Maybe I'm mad too. – Don't Panic Aug 31 '16 at 18:46

ScaisEdge · Answer 2 · 2016-08-31T18:18:28.030

2

You can get the cont by id this way

  select id, count(*) as ctn
  from table 
  group by id

and the count of same count with a second level count

 select ctn, count(*)
 from ( select id, count(*) as ctn
        from table 
        group by id) t
 group by ctn

edited Aug 31 '16 at 18:18

answered Aug 31 '16 at 18:10

ScaisEdge

131,976
10
91
107

1

@MichaelRudnerEvanchik reading the first sample seems this is what the OP is looking for ... (i hope ) – ScaisEdge Aug 31 '16 at 18:16
@scaisEdge It is indeed exactly what I was looking for. Since Don't Panic was just a tiny bit faster than you (with an impressively identical solution), though, I've accepted his answer, with many thanks to you both. :-) – Janus Bahs Jacquet Aug 31 '16 at 18:50

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

2 Answers2