mysql - sql 查询 - 如何创建 2 个级别的计数和组

Question

我想为所有组创建项目数的计数。

data
group_id, item_id
1, 1
1, 2
1, 3
2, 1
2, 2
3, 1
3, 2
4, 1

result
set, number_of_items
1, 3
2, 2
1, 1

如果支持，查询可能如下所示。

select count(cs_id) as count, count(count(cs_id) as count) as count2 
from max_ecardsent_group_list
group by cs_group_id, count

知道如何在不使用临时表的情况下通过查询来做到这一点吗？谢谢：-）

标签： mysqlsqlgroup-bycount

Answer 1

我认为您需要两个级别的聚合：

select num_items, count(*) as num_groups
from (select group_id, count(*) as num_items
      from groups
      group by group_id
     ) cnt
group by num_items;