SQL — GROUP BY & Aggregates

• Use COUNT, SUM, AVG, MIN, MAX for whole-table aggregates.
• Add GROUP BY col to compute per-bucket aggregates.
• Filter aggregated rows with HAVING.
• Combine groups with sorting and limiting for "top group" reports.

Run these on the students table from Lesson 17:

SELECT COUNT(*) FROM students;            -- 8
SELECT AVG(score) FROM students;          -- 77.5
SELECT MIN(age), MAX(age) FROM students;  -- 13, 14

Each aggregate collapses many rows into one. GROUP BY changes "all rows" to "all rows per bucket".

The five

COUNT(*)       number of rows
COUNT(col)     number of non-NULL values
SUM(col)       total
AVG(col)       average (mean)
MIN(col), MAX(col)

Per-group counts

SELECT class, COUNT(*) AS num_students
FROM students
GROUP BY class;

class    num_students
Form 1A          4
Form 1B          4

Per-group averages

SELECT class, AVG(score) AS avg_score
FROM students
GROUP BY class;

class    avg_score
Form 1A      80.75
Form 1B      74.25

Multiple aggregates

SELECT class,
       COUNT(*)   AS n,
       AVG(score) AS avg,
       MIN(score) AS lo,
       MAX(score) AS hi
FROM students
GROUP BY class;

Filter groups with HAVING

WHERE filters rows BEFORE grouping; HAVING filters groups AFTER:

-- Classes whose average is above 75
SELECT class, AVG(score) AS avg
FROM students
GROUP BY class
HAVING AVG(score) > 75;

Common pitfall — naked columns

This query is illegal in standard SQL:

-- ✗ Wrong: 'name' isn't aggregated and isn't in GROUP BY
SELECT class, name, AVG(score) FROM students GROUP BY class;

Every column in SELECT must either be in GROUP BY or be an aggregate. SQLite is lenient and runs it but the result is unpredictable. Don't rely on it.

SELECT
  class,
  COUNT(*)                                    AS students,
  ROUND(AVG(score), 1)                        AS avg_score,
  MIN(score)                                  AS lowest,
  MAX(score)                                  AS highest,
  COUNT(CASE WHEN score >= 80 THEN 1 END)     AS num_distinctions
FROM students
GROUP BY class
ORDER BY avg_score DESC;

class    students  avg_score  lowest  highest  num_distinctions
Form 1A         4       80.8      65       95                 2
Form 1B         4       74.2      55       92                 2

Read the diff

Two tricks worth knowing:

• ROUND(x, 1) rounds the average to one decimal — readable reports.
• COUNT(CASE WHEN condition THEN 1 END) counts only rows that match — the SQL idiom for "how many distinctions".

Five aggregate functions; one GROUP BY clause; HAVING filters AFTER. The rule of thumb: every non-aggregate column in SELECT must appear in GROUP BY. Combining groups + sorting + limiting gives you "top group", "biggest customer", "worst-performing day" — the building blocks of every dashboard.