Sql Query - How to Create 2 Levels of Count & Group
I Want to Create a Count of the Number of Items for All Groups. 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...
I want to create a count of the number of items for all groups.
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
The query, if supported, may look like this.
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
Any idea how I can do this with a query without using temporary table? Thanks:-)
1 Answer
I think you want two levels of aggregation:
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;
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
