GROUP BY and HAVING

How a Grouped Query Works

A useful logical sequence is FROM → WHERE → GROUP BY → aggregate calculations → HAVING → SELECT → ORDER BY → LIMIT. The optimizer may execute differently, but results follow these semantics.

GROUP BY: One Summary per Class

SELECT class_name,
       COUNT(*) AS students,
       SUM(marks) AS total_marks,
       ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY class_name
ORDER BY class_name;

All rows with the same class_name form one group. Grouping does not automatically sort; ORDER BY is explicit.

HAVING Filters Completed Groups

SELECT class_name,
       ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY class_name
HAVING AVG(marks) >= 85
ORDER BY class_name;

HAVING is evaluated after group aggregates exist. Repeat the aggregate expression for portable clarity; MySQL also permits many select aliases in HAVING.

WHERE vs HAVING Together

SELECT class_name,
       COUNT(*) AS active_students,
       ROUND(AVG(marks), 2) AS active_average
FROM students
WHERE status = 'Active'
GROUP BY class_name
HAVING COUNT(*) >= 2
ORDER BY class_name;

WHERE removes inactive Kabir first. X-B then has only Sana, so HAVING removes the one-student group.

Multiple Grouping Columns and ROLLUP

SELECT class_name, status, COUNT(*) AS students
FROM students
GROUP BY class_name, status
ORDER BY class_name, status;

SELECT class_name, COUNT(*) AS students
FROM students
GROUP BY class_name WITH ROLLUP;

The first creates one group per class/status combination. WITH ROLLUP adds an overall summary row represented by a rollup NULL; use MySQL's GROUPING() when real NULL grouping values must be distinguished from rollup rows.

ONLY_FULL_GROUP_BY and Correct Results

-- Ambiguous and normally rejected
SELECT class_name, full_name, AVG(marks)
FROM students
GROUP BY class_name;

Which full_name should represent a class? There is no correct single answer. Add full_name to grouping, aggregate it intentionally, or remove it. Do not disable strict mode merely to make an ambiguous query run.

Practice

1. Count students per status.
2. Show only classes with average at least 82.
3. Calculate active averages, then keep groups with two or more active students.
4. Create class/status groups.
5. Add a rollup total and label it safely using GROUPING().

Quick Summary

• GROUP BY creates one result per grouping combination.
• WHERE filters rows first; HAVING filters groups later.
• ORDER BY remains separate from grouping.
• ONLY_FULL_GROUP_BY protects against ambiguous results.
• WITH ROLLUP adds higher-level summaries.

Official References

• MySQL Handling of GROUP BY
• Aggregate Function Descriptions
• GROUP BY Modifiers / ROLLUP

References reviewed 14 August 2026.