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.

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.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database. 

Query:

CREATE TABLE demo_table(
NAME VARCHAR(20),
AGE int,
CITY VARCHAR(10));

Step 4: Insert data into a table

Query:

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;

Output:

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;

Query:

SELECT COUNT(*) FROM demo_table;

Output:

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.

Query:

SELECT AGE, COUNT(*) as COUNT from demo_table GROUP BY AGE;

Output:

• For counting the unique values in the CITY column.

SELECT CITY,COUNT(*) as COUNT from demo_table GROUP BY CITY;

Output: