SQL GROUP BY Clause Practice Questions with Solutions

The SQL GROUP BY clause is used to group rows that have the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to generate summarized reports.

Instead of calculating values for the entire table, GROUP BY performs calculations for each group separately. SQL GROUP BY Clause practice questions with solutions help to understand the concepts.

For example, if you want to know how many students are enrolled in each course, you can use:

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course;

Similarly, if you want to calculate the average marks of students in each city, you can write:

SELECT city,
       AVG(marks) AS average_marks

FROM students

GROUP BY city;

The GROUP BY clause is one of the most frequently used SQL features in:

• Business Reports
• Sales Analysis
• HR Dashboards
• Banking Systems
• Inventory Management
• Student Management Systems
• Financial Reports
• Data Analytics

What is SQL GROUP BY?

The GROUP BY clause groups records having the same value in one or more columns.

After grouping, SQL applies aggregate functions to each group individually.

Basic Syntax

SELECT column_name,
       aggregate_function(column_name)

FROM table_name

GROUP BY column_name;

Why Use GROUP BY?

The GROUP BY clause helps you:

• Generate summary reports
• Count records in each category
• Calculate totals for each department
• Find averages by city or course
• Compare business performance
• Build dashboards
• Analyze grouped data

Sample Table Used Throughout This Chapter

students

1. SQL Query to Count Students in Each Course

Problem Statement

A training institute wants to know how many students are enrolled in each course.

Write an SQL query to count students course-wise.

SQL Solution

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY course clause groups all students according to their course.

The COUNT() function counts the number of students in each course.

This type of report is commonly used for:

• Batch Strength
• Admission Reports
• Course-wise Analytics

Concepts Covered

• GROUP BY
• COUNT()
• Aggregate Functions

2. SQL Query to Count Students in Each City

Problem Statement

The administration wants to know how many students belong to each city.

Write an SQL query to display city-wise student counts.

SQL Solution

SELECT city,
       COUNT(*) AS total_students

FROM students

GROUP BY city;

Sample Output

Explanation

The GROUP BY city clause groups students according to their city.

The COUNT() function counts the number of students in every city.

This report helps institutions analyze regional student distribution.

Concepts Covered

• GROUP BY
• COUNT()
• City-wise Reports

3. SQL Query to Calculate Total Marks of Each Course

Problem Statement

A course coordinator wants to calculate the combined marks scored by students in each course.

Write an SQL query to display course-wise total marks.

SQL Solution

SELECT course,
       SUM(marks) AS total_marks

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY clause groups records according to the course.

The SUM() function calculates the total marks for each course.

Calculation:

Python

88 + 84 + 82 = 254

Java

91 + 90 = 181

SQL

95 + 89 = 184

This query is useful for:

• Department Reports
• Performance Analysis
• Academic Dashboards

Concepts Covered

• GROUP BY
• SUM()
• Aggregate Functions

4. SQL Query to Calculate the Average Marks of Each Course

Problem Statement

The academic coordinator wants to compare the average marks scored by students in each course.

Write an SQL query to display the average marks for every course.

Sample Table

SQL Solution

SELECT course,
       AVG(marks) AS average_marks

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY clause creates separate groups for each course.

The AVG() function then calculates the average marks for every group.

Python

(88 + 84 + 82)

÷ 3

= 84.67

Java

(91 + 90)

÷ 2

= 90.50

SQL

(95 + 89)

÷ 2

= 92.00

This report is commonly used for:

• Course Performance Analysis
• Faculty Evaluation
• Academic Reporting

Concepts Covered

• GROUP BY
• AVG()
• Aggregate Functions

5. SQL Query to Find the Highest Marks in Each Course

Problem Statement

The examination department wants to identify the highest marks scored in each course.

Write an SQL query to display the highest marks course-wise.

SQL Solution

SELECT course,
       MAX(marks) AS highest_marks

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY clause groups students according to their course.

The MAX() function returns the highest marks from each group.

This query is useful for:

• Merit Lists
• Performance Reports
• Faculty Reviews
• Student Analytics

Concepts Covered

• GROUP BY
• MAX()
• Aggregate Functions

6. SQL Query to Find the Lowest Marks in Each Course

Problem Statement

The academic department wants to identify the lowest marks scored in each course to find students who may need additional support.

Write an SQL query to display the lowest marks for every course.

Sample Table

SQL Solution

SELECT course,
       MIN(marks) AS lowest_marks

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY clause divides students into course-wise groups.

The MIN() function returns the smallest marks from each course.

This report helps identify students who may require extra academic guidance.

Concepts Covered

• GROUP BY
• MIN()
• Aggregate Functions

7. SQL Query to Calculate Total Marks of Students in Each City

Problem Statement

The regional education office wants to calculate the total marks scored by students from each city.

Write an SQL query to display city-wise total marks.

SQL Solution

SELECT city,
       SUM(marks) AS total_marks

FROM students

GROUP BY city;

Sample Output

Explanation

The query groups students by city.

The SUM() function then calculates the combined marks of students from each city.

This type of report is useful for regional performance analysis.

Concepts Covered

• GROUP BY
• SUM()
• City-wise Reports

8. SQL Query to Calculate the Average Marks of Each City

Problem Statement

A school administrator wants to compare the average marks of students from different cities.

Write an SQL query to display the average marks city-wise.

SQL Solution

SELECT city,
       AVG(marks) AS average_marks

FROM students

GROUP BY city;

Sample Output

Explanation

The AVG() function calculates the average marks for each city after grouping the records.

This report helps compare academic performance across regions.

Concepts Covered

• GROUP BY
• AVG()
• Aggregate Functions

9. SQL Query to Count Students by Age

Problem Statement

The administration wants to know how many students belong to each age group.

Write an SQL query to count students according to age.

SQL Solution

SELECT age,
       COUNT(*) AS total_students

FROM students

GROUP BY age;

Sample Output

Explanation

The GROUP BY age clause groups students according to their age.

The COUNT() function then counts how many students fall into each age category.

This report is useful for demographic analysis.

Concepts Covered

• GROUP BY
• COUNT()
• Age Analysis

10. SQL Query to Count Students by City and Course

Problem Statement

The institute wants to know how many students are enrolled in each course within every city.

Write an SQL query to display the city-wise and course-wise student count.

SQL Solution

SELECT city,
       course,
       COUNT(*) AS total_students

FROM students

GROUP BY city, course;

Sample Output

Explanation

This query groups records using two columns:

1. city
2. course

SQL creates a separate group for every unique combination of city and course.

This type of report is commonly used for:

• Branch-wise Enrollment
• Regional Course Analysis
• Admission Reports
• Business Dashboards

Concepts Covered

• GROUP BY
• Multiple Columns
• COUNT()
• Aggregate Functions

11. SQL Query to Find the Highest Marks in Each City

Problem Statement

The education department wants to identify the highest marks scored by students in each city.

Write an SQL query to display the highest marks city-wise.

Sample Table

SQL Solution

SELECT city,
       MAX(marks) AS highest_marks

FROM students

GROUP BY city;

Sample Output

Explanation

The GROUP BY city clause creates separate groups for each city.

The MAX() function returns the highest marks from every city.

This report helps compare the top-performing students across different locations.

Concepts Covered

• GROUP BY
• MAX()
• City-wise Analysis

12. SQL Query to Find the Lowest Marks in Each City

Problem Statement

The school management wants to identify the lowest marks scored by students in each city.

Write an SQL query to display the lowest marks city-wise.

SQL Solution

SELECT city,
       MIN(marks) AS lowest_marks

FROM students

GROUP BY city;

Sample Output

Explanation

The query groups students by city.

The MIN() function then returns the smallest marks from every city.

This report is useful for identifying areas where students may need additional academic support.

Concepts Covered

• GROUP BY
• MIN()
• Aggregate Functions

13. SQL Query to Calculate the Average Age of Students in Each Course

Problem Statement

The administration wants to calculate the average age of students enrolled in each course.

Write an SQL query to display course-wise average age.

SQL Solution

SELECT course,
       AVG(age) AS average_age

FROM students

GROUP BY course;

Sample Output

Explanation

The GROUP BY course clause groups students by course.

The AVG() function calculates the average age for each course.

This type of report helps institutions understand the age distribution of learners.

Concepts Covered

• GROUP BY
• AVG()
• Numeric Analysis

14. SQL Query to Count Students in Each Marks Category

Problem Statement

A school wants to classify students into performance categories:

• Excellent (90 and above)
• Good (80–89)
• Average (Below 80)

Write an SQL query to count the number of students in each category.

SQL Solution

SELECT
CASE
    WHEN marks >= 90 THEN 'Excellent'
    WHEN marks BETWEEN 80 AND 89 THEN 'Good'
    ELSE 'Average'
END AS performance,

COUNT(*) AS total_students

FROM students

GROUP BY performance;

Sample Output

Explanation

The CASE statement creates performance categories based on marks.

The GROUP BY clause groups students according to those categories.

Finally, the COUNT() function counts the students in each category.

This approach is commonly used in:

• Student Report Cards
• Employee Ratings
• Customer Segmentation
• Business Analytics

Concepts Covered

• GROUP BY
• CASE
• COUNT()
• Aggregate Functions

15. Real-World Example: Monthly Sales by Product Category

Problem Statement

An online store wants to calculate the total sales amount for each product category.

sales

Write an SQL query to display category-wise total sales.

SQL Solution

SELECT category,
       SUM(amount) AS total_sales

FROM sales

GROUP BY category;

Sample Output

Explanation

The query groups records by category.

The SUM() function calculates the total sales for each product category.

This type of report is widely used in:

• Sales Dashboards
• Business Intelligence
• Retail Analytics
• Revenue Reports
• Financial Reporting

Concepts Covered

• GROUP BY
• SUM()
• Business Reporting
• Sales Analytics

Chapter Summary

In this chapter, you learned how to use the SQL GROUP BY clause to organize records into meaningful groups and generate summarized reports using aggregate functions.

Unlike ordinary SQL queries that display every record individually, the GROUP BY clause combines rows with the same value into groups, allowing functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to calculate results for each group separately.

During this chapter, you practiced:

• Counting students in each course
• Counting students in each city
• Calculating total marks course-wise
• Calculating average marks course-wise
• Finding highest and lowest marks in each course
• Calculating city-wise totals and averages
• Grouping data using multiple columns
• Using CASE with GROUP BY
• Generating real-world sales reports

The GROUP BY clause is one of the most important SQL features used in reporting, dashboards, business intelligence, and data analytics.

Key Takeaways

• GROUP BY groups rows having the same values.
• It is commonly used with aggregate functions.
• COUNT() counts records in each group.
• SUM() calculates totals for every group.
• AVG() calculates averages group-wise.
• MIN() returns the smallest value in each group.
• MAX() returns the largest value in each group.
• Multiple columns can be used in a single GROUP BY statement.
• GROUP BY is widely used in reporting and dashboards.
• It is one of the most frequently asked SQL interview topics.

Frequently Asked Questions (FAQs)

1. What is the SQL GROUP BY clause?

The GROUP BY clause groups rows that have the same values in one or more columns.

Example:

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course;

2. Why is GROUP BY used?

GROUP BY is used to:

• Generate reports
• Summarize data
• Perform category-wise calculations
• Create dashboards
• Analyze grouped information

3. Can GROUP BY be used without aggregate functions?

Yes, but it is generally used together with aggregate functions.

Example:

SELECT city

FROM students

GROUP BY city;

This returns only unique city names.

4. Which aggregate functions are commonly used with GROUP BY?

The most commonly used aggregate functions are:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

5. Can GROUP BY use multiple columns?

Yes.

Example:

SELECT city,
       course,
       COUNT(*) AS total_students

FROM students

GROUP BY city, course;

This creates separate groups for each combination of city and course.

6. What is the difference between ORDER BY and GROUP BY?

7. Can GROUP BY be used with WHERE?

Yes.

Example:

SELECT course,
       AVG(marks) AS average_marks

FROM students

WHERE city = 'Delhi'

GROUP BY course;

The WHERE clause filters records before grouping.

8. Where is GROUP BY used in real-world applications?

The GROUP BY clause is commonly used in:

• Sales Reports
• Student Result Analysis
• Banking Reports
• Employee Performance Dashboards
• Inventory Management
• CRM Systems
• HR Analytics
• Business Intelligence
• Financial Reporting
• Data Analytics Projects

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar