Hone

Lessons · SQL · GROUP BY

One number per group

GROUP BY splits rows into groups by a column's value; aggregates then run once per group.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Orders per customer, errors per service, sales per month: per-group numbers are most of analytics.

How to think about it

Ask 'per what?'. That column goes in GROUP BY and in SELECT; everything else in SELECT must be an aggregate.

Worked example

SELECT rating, COUNT(*)
The group column, and a count per group.
FROM films
Where the rows live.
GROUP BY rating;
One row per rating.

Your turn

Average length per year.

SELECT year, AVG(minutes) FROM films GROUP BY ;

The trap

Filtering on an aggregate with WHERE. WHERE runs before grouping; HAVING runs after.

Practise GROUP BY on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.