SQL GROUP BY and HAVING Clause (CBSE Class 12 Computer Science - 083)

Aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() calculate values for an entire table. However, in many situations, we need these calculations separately for different categories, such as each city or department. SQL provides the GROUP BY clause to divide records into groups and the HAVING clause to filter those groups.

Learning Objectives

• Understand the GROUP BY clause.
• Use aggregate functions with GROUP BY.
• Understand the HAVING clause.
• Differentiate between WHERE and HAVING.
• Write practical SQL queries using grouped data.

---

Sample Student Table

StudentID	Name	Gender	Class	Section	City	Marks	DeptID
101	Riya	F	XII	A	Jaipur	95	1
102	Aman	M	XII	B	Delhi	91	2
103	Priya	F	XII	A	Jaipur	88	1
104	Rahul	M	XII	C	Mumbai	76	3
105	Neha	F	XII	B	Delhi	84	2
106	Arjun	M	XII	A	Jaipur	92	1
107	Simran	F	XII	C	Mumbai	81	3
108	Karan	M	XII	B	Delhi	69	2

---

What is GROUP BY?

The GROUP BY clause groups rows having the same values in one or more columns. Aggregate functions are then applied separately to each group.

---

Syntax

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

---

Example 1 : Count Students in Each City

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

Output

City	TotalStudents
Delhi	3
Jaipur	3
Mumbai	2

---

Example 2 : Average Marks in Each City

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

---

Example 3 : Highest Marks in Each Department

SELECT DeptID,
MAX(Marks) AS HighestMarks
FROM Student
GROUP BY DeptID;

---

Example 4 : Total Marks in Each Section

SELECT Section,
SUM(Marks) AS TotalMarks
FROM Student
GROUP BY Section;

---

Example 5 : Minimum Marks in Each City

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

---

Rules for GROUP BY

• Every selected column that is not an aggregate function must appear in the GROUP BY clause.
• GROUP BY is usually used with aggregate functions.
• One query can group records using one or more columns.

---

Grouping by Multiple Columns

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

---

HAVING Clause

The HAVING clause filters grouped records. It is used after the GROUP BY clause.

---

Syntax

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

---

Example 1 : Cities Having More Than Two Students

SELECT City,
COUNT(*) AS TotalStudents
FROM Student
GROUP BY City
HAVING COUNT(*) > 2;

Output

City	TotalStudents
Delhi	3
Jaipur	3

---

Example 2 : Departments with Average Marks Above 85

SELECT DeptID,
AVG(Marks) AS AverageMarks
FROM Student
GROUP BY DeptID
HAVING AVG(Marks) > 85;

---

Example 3 : Sections Having Total Marks Above 250

SELECT Section,
SUM(Marks) AS TotalMarks
FROM Student
GROUP BY Section
HAVING SUM(Marks) > 250;

---

Using WHERE and GROUP BY Together

The WHERE clause filters individual rows before grouping takes place.

SELECT City,
COUNT(*) AS TotalStudents
FROM Student
WHERE Marks >= 80
GROUP BY City;

---

Using WHERE, GROUP BY and HAVING Together

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

---

Execution Order

FROM
   │
   ▼
WHERE
   │
   ▼
GROUP BY
   │
   ▼
HAVING
   │
   ▼
SELECT
   │
   ▼
ORDER BY (if used)

---

Difference Between WHERE and HAVING

WHERE	HAVING
Filters individual rows.	Filters groups.
Used before GROUP BY.	Used after GROUP BY.
Cannot use aggregate functions directly.	Commonly used with aggregate functions.
Works on records.	Works on grouped records.

---

Summary of GROUP BY Queries

Query	Purpose
COUNT(*)	Count records in each group.
SUM()	Total of each group.
AVG()	Average of each group.
MAX()	Highest value in each group.
MIN()	Lowest value in each group.

---

Common Errors

• Using aggregate functions in the WHERE clause instead of HAVING.
• Selecting columns that are neither grouped nor aggregated.
• Forgetting to write GROUP BY before HAVING.
• Confusing WHERE with HAVING.
• Using HAVING without understanding that it filters groups, not rows.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is the purpose of GROUP BY?

GROUP BY groups rows having the same values so that aggregate functions can be applied to each group.

2. Why is the HAVING clause used?

HAVING filters groups created by the GROUP BY clause.

3. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, whereas HAVING filters grouped records after grouping.

4. Can GROUP BY be used without aggregate functions?

Yes, although it is most commonly used with aggregate functions to summarize grouped data.

5. Which clause is executed first, WHERE or HAVING?

The WHERE clause is executed before GROUP BY, whereas HAVING is executed after GROUP BY.

---

Summary

• GROUP BY divides records into groups based on one or more columns.
• Aggregate functions calculate summary values for each group.
• HAVING filters grouped results.
• WHERE filters rows before grouping.
• HAVING filters groups after grouping.
• GROUP BY and HAVING are commonly used together in SQL reports and CBSE practical questions.