By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table Roster_table (Group_ID int, Group_Name varchar(20));
insert into Roster_table values (42, 'Red Dragon'), (32, 'Green Lizard'), (19, 'Blue Falcon')
, (42, 'Red Dragon'), (42, 'Red Dragon'), (19, 'Blue Falcon');
6 rows affected
SELECT Group_ID
, MAX(Group_Name) Group_Name
, COUNT(1) tot_count
FROM Roster_table
GROUP BY Group_ID
ORDER BY tot_count DESC
| group_id | group_name | tot_count |
|---|---|---|
| 42 | Red Dragon | 3 |
| 19 | Blue Falcon | 2 |
| 32 | Green Lizard | 1 |