Lessons · SQL · GROUP BY an expression
Bucketing by a computed value
You can GROUP BY an expression, not only a column: the month of a date, the first letter of a name.
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
Time series (per month, per week) and histograms are groupings by a derived value.
How to think about it
What is the bucket, and how do I compute it? Compute the bucket in SELECT, alias it, and group by the same expression (or its alias where the database allows).
Worked example
SELECT substr(placed, 1, 7) AS month, COUNT(*)YYYY-MM from YYYY-MM-DD.
FROM orders GROUP BY month ORDER BY month;One row per month.
Your turn
Count films per decade.
SELECT (year / 10) * 10 AS decade, COUNT(*) FROM films GROUP BY ;
Run a query against real tables
The trap
Selecting a column that is neither grouped nor aggregated. Some databases error; SQLite picks an arbitrary row.
Practise GROUP BY an expression on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.