Lessons · SQL · grouped SELECTs

What SELECT may contain after GROUP BY

After GROUP BY, each output row is one group, so SELECT may hold only the grouped columns and aggregates over the 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

The 'which title goes with this average' confusion is the most common GROUP BY error.

For every column in SELECT ask: is it in GROUP BY, or wrapped in an aggregate? If neither, it does not belong.

SELECT rating, ROUND(AVG(minutes), 1) AS avg_len, MAX(year)

Grouped column, two aggregates.

FROM films GROUP BY rating;

One output row per rating.

Average amount per customer.

SELECT customer_id, (amount) FROM orders GROUP BY customer_id;

Wanting the row that has the max. That is a different query: ORDER BY ... LIMIT 1, or a subquery.

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