E0012 misplaced-aggregate

Aggregate or window function where it is not allowed.

What it reports

An aggregate function (count, sum, max, ...) is used in WHERE, JOIN ... ON, GROUP BY, UPDATE SET or VALUES; an aggregate is nested inside another aggregate; or a window function (... OVER (...)) is used in WHERE, JOIN ... ON, GROUP BY, HAVING, UPDATE SET or VALUES. Aggregates are computed after WHERE filters the rows, so the database rejects these.

Example

SELECT user_id FROM orders WHERE count(*) > 1;

error[E0012]: Aggregate function 'count' is not allowed in WHERE
  --> q.sql:1:34
    |
  1 | SELECT user_id FROM orders WHERE count(*) > 1;
    |                                  ^^^^^
    = help: Filter on aggregates in HAVING, or aggregate in a subquery

Fixed:

SELECT user_id FROM orders GROUP BY user_id HAVING count(*) > 1;

Notes

Aggregates inside a subquery belong to the subquery (WHERE total > (SELECT avg(total) FROM orders) is fine), and a window function over an aggregate (sum(count(*)) OVER ()) is fine. Only built-in aggregates are recognized, by unqualified name: a schema-qualified call such as stats.max(x) may be a user-defined function and is not reported. SQLite's min / max with several arguments are scalar functions.

Configuring

# sqlsift.toml
[rules]
misplaced-aggregate = "warn"   # or "off"

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