SQL – Using GROUP BY to COUNT the Number of Rows For Each Unique Entry in a Column
In this article, we will see how to use GROUP BY to count the number of rows for each unique entry in a given table. Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table.
Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
Now, for the demonstration follow the below steps:
Step 1: Create a database
we can use the following command to create a database called geeks.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have the following demo_table in our geek’s database.
CREATE TABLE demo_table( NAME VARCHAR(20), AGE int, CITY VARCHAR(10));
Step 4: Insert data into a table
INSERT INTO demo_table VALUES ('Romy',23,'Delhi'), ('Pushkar',23,'Delhi'), ('Nikhil',24,'Punjab'), ('Rinkle',23,'Punjab'), ('Samiksha',23,'Banglore'), ('Ashtha',24,'Banglore'), ('Satish',30,'Patna'), ('Girish',30,'Patna');
Step 5: View the content
Execute the below query to see the content of the table
SELECT * FROM demo_table;
Step 6: use of COUNT without ORDER BY statement
- COUNT(*) counts all rows
- COUNT(column_name) counts non-NULLs only in the specified column name.
Syntax(count all rows):
SELECT COUNT(*) FROM table_name;
SELECT COUNT(*) FROM demo_table;
The result is 8, as we have 8 entries in our demo_table.
Step 7: use GROUP BY
- For counting the unique values in the AGE column.
SELECT AGE, COUNT(*) as COUNT from demo_table GROUP BY AGE;
- For counting the unique values in the CITY column.
SELECT CITY,COUNT(*) as COUNT from demo_table GROUP BY CITY;