Lesson 16 — Grouping Data

GROUP BY

GROUP BY splits rows into groups and applies aggregate functions to each group separately — the foundation of all summary reports.

SQL — Basic GROUP BY 
-- Group by course — one result row per course:
SELECT
    course,
    COUNT(*)   AS learner_count,
    AVG(mark)  AS avg_mark,
    MIN(mark)  AS lowest,
    MAX(mark)  AS highest
FROM   learners
GROUP  BY course;

-- Group by city:
SELECT
    city,
    COUNT(*) AS learners_in_city
FROM   learners
GROUP  BY city
ORDER  BY learners_in_city DESC;
SQL — Multi-column GROUP BY 
-- Multiple GROUP BY columns:
SELECT
    city,
    course,
    COUNT(*) AS count
FROM   learners
GROUP  BY city, course
ORDER  BY city, course;

-- GROUP BY with WHERE (filter rows BEFORE grouping):
SELECT
    course,
    AVG(mark) AS avg_pass_mark
FROM   learners
WHERE  mark >= 50          -- only include passing learners
GROUP  BY course;
SQL — GROUP BY in subquery 
-- Using GROUP BY results in a subquery:
SELECT
    l.full_name,
    l.course,
    l.mark,
    course_stats.avg_mark AS course_average,
    l.mark - course_stats.avg_mark AS above_average_by
FROM learners l
JOIN (
    SELECT course, AVG(mark) AS avg_mark
    FROM   learners
    GROUP  BY course
) AS course_stats ON l.course = course_stats.course
ORDER BY course, above_average_by DESC;
SQL — ROLLUP 
-- ROLLUP — adds subtotals and grand total:
SELECT
    COALESCE(city, 'ALL CITIES') AS city,
    COALESCE(course, 'ALL COURSES') AS course,
    COUNT(*) AS count
FROM   learners
GROUP  BY city, course WITH ROLLUP;  -- MySQL
-- GROUP BY ROLLUP(city, course);   -- standard SQL

Practice Task

Your Turn

Write queries: (1) learner count and average mark per course, sorted by average descending; (2) learner count per city per province (two-column group); (3) count of passing vs failing learners per course (use conditional COUNT with CASE); (4) find courses where the average mark is above 70 — try both with HAVING (next lesson) and by putting the GROUP BY result in a subquery.

Common Mistakes

  • Selecting a non-aggregated, non-grouped column — most databases reject this (SQL standard) or return arbitrary values (MySQL with ONLY_FULL_GROUP_BY disabled).
  • WHERE vs HAVING confusion — WHERE filters rows before grouping; HAVING filters groups after.
  • GROUP BY column not in SELECT — technically valid but confusing.
  • GROUP BY on a calculated expression must be repeated in the GROUP BY clause.

Professional Tip

Every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function. This is the golden rule of GROUP BY.

Mini Quiz

Which clause filters rows BEFORE GROUP BY, and which filters groups AFTER?