Can GROUP BY take an expression instead of a column, and how do you bucket a timestamp by day?

## Keys are expressions The grouping key list is a list of *value expressions*, evaluated over the columns available after `FROM` and `WHERE`. A bare column name is just the simplest expression. All of these are legal keys: ```sql GROUP BY region GROUP BY LOWER(email) GROUP BY EXTRACT(YEAR FROM order_date) GROUP BY CAST(created_at AS DATE) GROUP BY CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END ``` The engine computes the expression for each row, then partitions rows by the computed value. Everything else about grouping is unchanged: one output row per distinct computed value, NULL results collapsing into a single group, cardinality equal to the number of distinct values the expression produces. ## The granularity bug The most-asked version of this question is a report that does not group: ```sql -- Intended: signups per day. Actual: one row per distinct instant. SELECT created_at, COUNT(*) FROM signups GROUP BY created_at; ``` If `created_at` is a `TIMESTAMP` with sub-second precision, virtually every row has a unique value, so virtually every row becomes its own group. The output has the right *shape* — a key column and a count — but the count is `1` almost everywhere and the row count matches the table. Nothing is broken in the engine; the query genuinely asked for one group per distinct instant. The fix is to make the key coarser: ```sql SELECT CAST(created_at AS DATE) AS signup_day, COUNT(*) AS signups FROM signups GROUP BY CAST(created_at AS DATE) ORDER BY signup_day; ``` ## Choosing the bucket expression - **Day buckets**: `CAST(ts AS DATE)` is the most portable form in engines that have a `DATE` type. Engines also ship their own truncation helpers — PostgreSQL's `DATE_TRUNC`, Oracle's `TRUNC`, MySQL's `DATE()` — whose names and arguments differ, so a query using one is not portable. SQLite has no dedicated date type and stores date/time values as text or numbers, so it uses its own date functions instead. - **Month or year buckets**: `EXTRACT(YEAR FROM d)` and `EXTRACT(MONTH FROM d)` are standard. Beware of extracting month alone across multiple years — `GROUP BY EXTRACT(MONTH FROM d)` merges every January in history into one group. If you want calendar months, group by year and month together, or by a month-truncated value. - **Bands and categories**: a `CASE` expression turns a continuous measure into named buckets, which is the standard way to produce a histogram-style grouping. ## Repeating the expression When the same expression appears in both `SELECT` and `GROUP BY`, you generally write it twice; a query that displays `CAST(created_at AS DATE)` but groups by raw `created_at` is *not* the same query, and it will trip the rule about what a grouped `SELECT` list may contain. Give the expression an alias for the output column's benefit, and keep the `GROUP BY` list explicit about which expression defines the groups. ## Non-injective expressions merge rows on purpose `LOWER(email)` puts `[email protected]` and `[email protected]` in one group. `CAST(ts AS DATE)` puts every instant in a day together. That merging is the entire point of an expression key, but it is worth stating explicitly, because the merged rows are no longer distinguishable in the output. If the report needs to show the raw values that fell into a bucket, grouping has already discarded them — you would need to aggregate them (`MIN`, `MAX`, a string aggregate) or not collapse at all. ## Time zones deserve a deliberate answer A "per day" bucket only means something relative to a time zone. If timestamps are stored in UTC and the audience reads local days, truncating the UTC value produces buckets that are shifted by hours and can move a chunk of traffic into the wrong day. The conversion belongs in the expression, using whatever conversion syntax the engine offers, and it should be a conscious decision recorded near the query rather than an accident of storage. ## Determinism A grouping expression should be deterministic over the row's values. Grouping by something that varies between evaluations — a random value, or a clock reading — produces a result that is not reproducible and is not what any reader expects. Keep grouping keys pure functions of the columns. ## Summary The grouping key is whatever expression you write; the number of groups is the number of distinct values that expression yields. Reports that "don't group" are almost always grouping on a key that is finer than the reader's intended bucket, and coarsening the expression — usually by truncating a timestamp — is the whole fix.