SQL GROUP BY Clause

The GROUP BY clause is used to arrange rows having the same values in one or more columns into groups. It is commonly used with aggregate functions to calculate summaries such as total, average, maximum, minimum, and count for each group.

Without GROUP BY, aggregate functions calculate a single result for the entire table. With GROUP BY, the calculation is performed separately for each group.

---

Why Use GROUP BY?

• Organize records into groups.
• Generate category-wise reports.
• Calculate group-wise totals and averages.
• Simplify data analysis.
• Create meaningful summaries.

---

Sample Table : Student

RollNo	Name	City	Marks
101	Amit	Jaipur	85
102	Neha	Delhi	92
103	Rohan	Jaipur	78
104	Priya	Delhi	88
105	Karan	Mumbai	81
106	Meera	Jaipur	91

---

Syntax of GROUP BY

SELECT column_name,
       aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

---

Example 1: Count Students City-wise

SELECT City,
       COUNT(*)
FROM Student
GROUP BY City;

Output

City	COUNT(*)
Delhi	2
Jaipur	3
Mumbai	1

---

Example 2: Average Marks City-wise

SELECT City,
       AVG(Marks)
FROM Student
GROUP BY City;

---

Example 3: Total Marks City-wise

SELECT City,
       SUM(Marks)
FROM Student
GROUP BY City;

---

Example 4: Highest Marks in Each City

SELECT City,
       MAX(Marks)
FROM Student
GROUP BY City;

---

Example 5: Lowest Marks in Each City

SELECT City,
       MIN(Marks)
FROM Student
GROUP BY City;

---

Using Multiple Columns in GROUP BY

More than one column can be used for grouping.

SELECT City,
       Marks,
       COUNT(*)
FROM Student
GROUP BY City, Marks;

---

GROUP BY with WHERE Clause

The WHERE clause filters rows before grouping.

SELECT City,
       AVG(Marks)
FROM Student
WHERE Marks >= 80
GROUP BY City;

---

Rules of GROUP BY

• Every column in the SELECT list must either:
  • Be included in the GROUP BY clause, or
  • Be used inside an aggregate function.
• GROUP BY is usually used with aggregate functions.
• Grouping is performed after filtering with WHERE.

---

Without GROUP BY vs With GROUP BY

Without GROUP BY

SELECT AVG(Marks)
FROM Student;

Returns a single average for all students.

---

With GROUP BY

SELECT City,
       AVG(Marks)
FROM Student
GROUP BY City;

Returns a separate average for each city.

---

Common Aggregate Functions Used with GROUP BY

Function	Purpose
COUNT()	Number of records
SUM()	Total value
AVG()	Average value
MAX()	Highest value
MIN()	Lowest value

---

Real-Life Applications

Situation	Example Query
Department-wise Employees	COUNT(*)
City-wise Students	COUNT(*)
Branch-wise Sales	SUM(Sales)
Class-wise Average Marks	AVG(Marks)
Product-wise Revenue	SUM(Revenue)

---

Common Errors

Error	Reason
Unknown Column	Incorrect column name.
Column not in GROUP BY	Selected column is neither grouped nor aggregated.
Syntax Error	Incorrect SQL syntax.

---

Quick Revision

Concept	Remember
GROUP BY	Creates groups of similar values.
COUNT()	Counts records in each group.
SUM()	Calculates total for each group.
AVG()	Calculates average for each group.
MAX()	Finds highest value in each group.
MIN()	Finds lowest value in each group.

---

CBSE Exam Tips

• Use GROUP BY whenever aggregate values are required for different categories.
• Every non-aggregated column in the SELECT statement must appear in the GROUP BY clause.
• WHERE filters rows before grouping.
• Practice queries using COUNT(), SUM(), AVG(), MAX(), and MIN() with GROUP BY.
• Read the question carefully to identify the column on which grouping should be performed.

---

Summary

The GROUP BY clause is used to group rows with similar values and apply aggregate functions to each group separately. It is widely used for generating reports, analyzing data, and summarizing information. Mastering GROUP BY is essential for solving CBSE Class 12 Informatics Practices SQL queries and real-world database problems.