E0016 grouping-error

Column must appear in GROUP BY or be used in an aggregate.

What it reports

In a query that aggregates (it has GROUP BY or HAVING, or calls an aggregate function such as count), the select list, HAVING or ORDER BY uses a column outside an aggregate function that GROUP BY doesn't group. Each group has many values for that column, so PostgreSQL rejects the query.

Example

SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.email;

error[E0016]: Column 'u.name' must appear in GROUP BY or be used in an aggregate function
  --> q.sql:1:10
    |
  1 | SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.email;
    |          ^^^^
    = help: Add 'u.name' to GROUP BY, or aggregate it (for example max(u.name))

Fixed:

SELECT u.name, count(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;

Notes

A column counts as grouped when GROUP BY lists it (also by position or output alias), when it is used inside a grouped expression (GROUP BY lower(name) allows lower(name)), or when GROUP BY lists the whole primary key of its table, as PostgreSQL allows (above, u.id makes every column of users grouped).

Checked for PostgreSQL and Redshift only: MySQL's result depends on ONLY_FULL_GROUP_BY, and SQLite allows such columns. To avoid false positives nothing is reported for GROUPING SETS / ROLLUP / CUBE, joins with USING, relations whose columns are unknown, SELECT *, statements with template tags (dbt macros can expand to GROUP BY columns), and columns inside calls of functions sqlsift doesn't know, which may be user-defined aggregates.

Configuring

# sqlsift.toml
[rules]
grouping-error = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0016. The examples use the schema on the Rules page.