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

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

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

知道如何在不使用临时表的情况下通过查询执行此操作吗?谢谢:-)

评论
HeroA
HeroA

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

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;
点赞
评论