SQL GROUP BY and HAVING

GROUP BY puts rows with the same value into groups, so an aggregate function can give one answer for each group.

GROUP BY Syntax

SELECT column, AGGREGATE_FUNCTION(column)
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column;

Count Students per City

Without GROUP BY, COUNT(*) gives one number for the whole table. With it, you get one number per city:

SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
ORDER BY total DESC;

The database makes three groups (Karachi, Lahore, Islamabad) and counts the rows in each.

Several Aggregates per Group

SELECT city, COUNT(*) AS total, MAX(marks) AS best
FROM students
GROUP BY city
ORDER BY total DESC;

GROUP BY with a Join

Joins and grouping work well together. This report shows how many students each course has and their average marks:

SELECT c.title,
       COUNT(s.id) AS students,
       ROUND(AVG(s.marks), 1) AS avg_marks
FROM courses AS c
LEFT JOIN students AS s ON s.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;

We used COUNT(s.id), not COUNT(*), so the course with no students shows 0 instead of 1.

HAVING: Filter the Groups

WHERE filters rows before they are grouped. HAVING filters groups after the aggregate is calculated. That is why aggregates can go in HAVING but not in WHERE.

Cities with more than one student:

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

Courses whose students average more than 75 marks:

SELECT c.title, AVG(s.marks) AS avg_marks
FROM students AS s
JOIN courses AS c ON s.course_id = c.id
GROUP BY c.title
HAVING AVG(s.marks) > 75;

Web Development is left out, because its average is 71.

WHERE and HAVING Together

SELECT city, MAX(marks) AS top
FROM students
WHERE age > 18
GROUP BY city
HAVING MAX(marks) > 80
ORDER BY top DESC;

First Hamza (18) is removed by WHERE. Then the groups are made, and Karachi is removed by HAVING because its best mark is 78.

Exercise