ungrouped-column¶
Default severity: error
What it does¶
Reports a column that a grouped query selects without grouping by it or
aggregating it. A query is grouped by a GROUP BY, or by an aggregate such as
count(*) in its select list.
[tool.sqlty]
schema = "schema.sql"
[tool.sqlty.rules]
ungrouped-column = "warn"
schema.sql:
CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT NOT NULL, total INTEGER NOT NULL);
from sqlty import SQL
# error: [ungrouped-column] "column `orders.total` must appear in GROUP BY or be used in an aggregate function"
q = SQL[tuple[str, int]]("--sql\nSELECT status, total FROM orders GROUP BY status")
Open in playgroundA column is grouped when a key names it, by position (GROUP BY 1) or by its
result alias too, or when a key is the expression it sits in. Grouping by a
table's primary key covers the table's other columns, as PostgreSQL's functional
dependency does:
from sqlty import SQL
# error:
by_id = SQL[tuple[int, str, int]]("--sql\nSELECT id, status, count(*) FROM orders GROUP BY id")
by_position = SQL[tuple[str, int]]("--sql\nSELECT status, count(*) FROM orders GROUP BY 1")
Open in playground*, t.* and a whole-row reference such as row_to_json(o) read every column
of their table, so each column must be grouped, or the table's primary key.
Why is this bad?¶
PostgreSQL rejects the query: column "orders.total" must appear in the GROUP BY
clause or be used in an aggregate function. Each group has one row in the
result, so a column that varies within a group has no single value.
How to fix¶
Group by the column, or aggregate it: sum(total), max(total).
Configuration¶
An error by default: the database rejects such SQL. Lower or turn it off:
[tool.sqlty.rules]
ungrouped-column = "warn"
Needs a schema: only columns of
schema tables are judged. Judged in the postgres dialect; MySQL's
ONLY_FULL_GROUP_BY follows more dependencies (unique keys, equalities in
WHERE) and is not modelled.