MySQL GROUP BY and HAVING

GROUP BY puts rows with the same value in a column into groups, and an aggregate function calculates one value for each group.

SELECT city, COUNT(*) AS students, AVG(marks) AS average_marks
FROM students
GROUP BY city
ORDER BY city;

There is one row for each city. Delhi has two students with an average of (85 + 65) / 2 = 75.

---

HAVING: filter the groups

WHERE filters rows before grouping. HAVING filters the groups after grouping, so it can use aggregate functions.

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY city;

---

WHERE and HAVING together

SELECT city, AVG(marks) AS average_marks
FROM students
WHERE marks > 50
GROUP BY city
HAVING AVG(marks) > 80
ORDER BY city;

First the rows with marks 50 or less are removed (Neha). Then the cities are grouped. Mumbai now has only Maria (92), so its average is 92. Delhi (75) and Pune (78) are not above 80.

---

Order of the clauses

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

---

Summary

• GROUP BY makes groups, aggregates calculate per group.
• HAVING filters groups, WHERE filters rows.
• Next: MySQL JOINs.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.