Lesson 4 of 12 · 0 of 2 exercises solved

COUNT, SUM & GROUP BY

turn many rows into a single answer, such as a count or an average. GROUP BY gives you that answer for each group, for example per age or per country.

-- Aggregates over the whole table:
SELECT COUNT(*) FROM students;          -- number of rows
SELECT AVG(grade) FROM students;        -- average
SELECT MAX(grade), MIN(grade) FROM students;
SELECT SUM(grade) FROM students;

-- COUNT(column) skips NULLs; COUNT(*) counts all rows:
SELECT COUNT(email) FROM students;

GROUP BY splits rows into buckets and runs the aggregate per bucket. 'Average grade per age' means: bucket rows by age, then AVG each bucket. Every selected column must either be in the GROUP BY or wrapped in an aggregate — otherwise the can't know which row's value to show.

-- Average grade per age group:
SELECT age, AVG(grade) AS avg_grade, COUNT(*) AS n
FROM students
GROUP BY age;

-- Filter groups with HAVING (WHERE filters rows, HAVING filters groups):
SELECT age, AVG(grade) AS avg_grade
FROM students
GROUP BY age
HAVING AVG(grade) > 85;

-- Full order of clauses:
-- SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

Common question

Can a column be in SELECT without being in GROUP BY?

Aggregation squashes many rows into one. Without GROUP BY, the whole result becomes a single row; with it, each distinct combination of the grouping columns becomes one output row, and aggregates like COUNT, SUM and AVG summarise the rows inside. The rule that every selected column must be grouped or aggregated follows directly: one output row cannot hold several different values.

Read a grouping query in the order it runs (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) and put each condition at the step where its inputs exist. Remember that aggregates skip , and only COUNT(*) counts them.

Source: The standard requires every non-aggregated column in the select to be functionally dependent on the grouping columns.

Common mix-ups

Each of these sounds reasonable. Think about why it is wrong, then open the explanation.

SELECT name, AVG(grade) FROM students GROUP BY age should work and show each group's name.
WHERE can filter on an aggregate, as in WHERE COUNT(*) > 1.
COUNT(*) and COUNT(email) are the same.

Practice

For each age that has more than one student, show the age and the average grade as avg_grade.

Exercise

Group and filter groups

3 checks

SELECT age, AVG(grade) AS avg_grade
FROM students
GROUP BY age;

Output

Press Run to try your code, then Check answer to grade it.

How much has each customer spent? Return customer_id and total_spent (the sum of order totals), largest first. Only count orders that are not cancelled.

Exercise

Sum per customer

4 checks

SELECT customer_id, ... FROM orders

Output

Press Run to try your code, then Check answer to grade it.

Check yourself

Quick check

Why does WHERE AVG(grade) > 85 fail?

Finished reading?2 exercises still open. You can come back to them later.

Tip: use the ← and → keys to move between lessons.