CODEWITHSUNDEEP
sql
jackInTrouble
jackInTrouble

Reputation: 3

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

Upvotes: 0

Views: 214

Answers (2)

jackInTrouble
jackInTrouble

Reputation: 3

Based on Rahul Biswas's answer:

Solution without using Max function

SELECT Group_ID, Group_Name, COUNT(*)
FROM Roster_table
GROUP BY Group_ID, Group_Name
ORDER BY COUNT(*) DESC

Credit goes to Eric S.

Upvotes: 0

Rahul Biswas
Rahul Biswas

Reputation: 3467

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

Upvotes: 1

Related Questions