GROUP BY & HAVING Clause

1. What is GROUP BY?

The GROUP BY clause groups rows that have the same values in specified columns and applies aggregate functions on each group.

• Used with aggregate functions
• Groups similar values
• Produces summarized output

2. GROUP BY Syntax

SELECT column_name, AGG_FUNCTION(column)
FROM table_name
GROUP BY column_name;

3. GROUP BY Example

SELECT CourseID, COUNT(*) AS TotalStudents
FROM student
GROUP BY CourseID;

This query shows the number of students in each course.

4. What is HAVING Clause?

The HAVING clause is used to apply conditions on grouped data after GROUP BY.

• Used with GROUP BY
• Works on aggregated results
• Cannot be replaced by WHERE

5. HAVING Syntax

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

6. HAVING Example

SELECT CourseID, COUNT(*) AS TotalStudents
FROM student
GROUP BY CourseID
HAVING COUNT(*) > 5;

This query displays only those courses having more than 5 students.

7. WHERE vs HAVING

WHERE                          HAVING
----------------------------  ------------------------------
Filters rows                  Filters groups
Used before GROUP BY          Used after GROUP BY
Cannot use aggregates         Uses aggregate functions

8. WHERE + GROUP BY + HAVING

SELECT CourseID, AVG(Marks)
FROM student
WHERE Marks > 40
GROUP BY CourseID
HAVING AVG(Marks) > 60;

9. SQL Execution Order

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Practice Questions

1. What is GROUP BY?
2. Why HAVING clause is required?
3. Difference between WHERE and HAVING.
4. Can HAVING be used without GROUP BY?
5. Write a query using GROUP BY and HAVING.

Practice Task