Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL – Using GROUP BY to COUNT the Number of Rows For Each Unique Entry in a Column

  • Last Updated : 18 Oct, 2021

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.

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!