GROUP BY, HAVING & ROLLUP

GROUP BY

SELECT department, COUNT(*) AS total, ROUND(AVG(cgpa),2) AS avg_cgpa
FROM students
GROUP BY department
ORDER BY total DESC;

Group by multiple columns to create finer buckets:

SELECT department, YEAR(admitted_on) AS batch, COUNT(*) AS total
FROM students
GROUP BY department, YEAR(admitted_on)
ORDER BY department, batch;

WHERE vs HAVING

	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Individual rows	Groups
Aggregates allowed	No	Yes
Can use SELECT aliases	No	Yes

SELECT department, COUNT(*) AS total, AVG(cgpa) AS avg_cgpa
FROM students
WHERE admitted_on >= '2023-01-01'   -- filter rows first
GROUP BY department
HAVING total >= 5 AND avg_cgpa > 3.0  -- then filter groups
ORDER BY avg_cgpa DESC;

Put a condition in WHERE whenever you can — filtering rows early means fewer rows to group, which is always faster.

ONLY_FULL_GROUP_BY

MySQL 5.7+ enables the ONLY_FULL_GROUP_BY SQL mode by default. Every non-aggregated column in SELECT must appear in GROUP BY:

-- ERROR: name_en is not grouped and not aggregated
SELECT department, name_en, COUNT(*) FROM students GROUP BY department;

-- fix 1: aggregate it
SELECT department, MAX(name_en), COUNT(*) FROM students GROUP BY department;

-- fix 2: group by it
SELECT department, name_en, COUNT(*) FROM students GROUP BY department, name_en;

-- fix 3: mark it functionally dependent with ANY_VALUE()
SELECT department, ANY_VALUE(name_en), COUNT(*) FROM students GROUP BY department;

Do not disable this mode. It exists because the old behaviour silently returned an arbitrary row's value.

WITH ROLLUP

ROLLUP adds subtotal and grand-total rows:

SELECT
  IFNULL(department, 'ALL DEPARTMENTS') AS department,
  IFNULL(YEAR(admitted_on), 'ALL YEARS') AS batch,
  COUNT(*) AS total
FROM students
GROUP BY department, YEAR(admitted_on) WITH ROLLUP;

Subtotal rows carry NULL in the rolled-up columns; use GROUPING() to tell a real NULL from a rollup marker:

SELECT department, COUNT(*),
       GROUPING(department) AS is_total
FROM students GROUP BY department WITH ROLLUP;

Useful reporting patterns

-- groups with duplicates (data quality check)
SELECT email, COUNT(*) AS c
FROM students GROUP BY email HAVING c > 1;

-- top 3 departments by average CGPA
SELECT department, ROUND(AVG(cgpa),2) AS avg_cgpa
FROM students GROUP BY department
ORDER BY avg_cgpa DESC LIMIT 3;