SQL query count rows with the same entry

Given a dataset Roster_table as such:

Group ID	Group Name	Name	Phone
42	Red Dragon	Jon	123455678
32	Green Lizard	Liz	932143211
19	Blue Falcon	Ben	134554678
42	Red Dragon	Reed	432143211
42	Red Dragon	Brad	231314155
19	Blue Falcon	Chad	214124412

How do I get the following query output combining rows with the same Group ID from the dataset, and the new column Count in descending order:

Group ID	Group Name	Count
42	Red Dragon	3
19	Blue Falcon	2
32	Green Lizard	1

SELECT * FROM Roster_table

Answer

Please try this where alias tot_count is used in ORDER BY clause.

-- PostgreSQL(v11)
SELECT Group_ID
     , MAX(Group_Name) Group_Name
     , COUNT(1) tot_count
FROM Roster_table
GROUP BY Group_ID
ORDER BY tot_count DESC;

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=b66f9f0d40e804e89be12e3530fe00a0