fix/duckdb/where-clause-cannot-contain-aggregates
DuckDB error

WHERE clause cannot contain aggregates

Updated Aug 27, 20264-min read
TL;DR

The WHERE clause runs on individual rows before any grouping happens, so aggregate functions like COUNT, SUM, AVG, MIN, MAX have nothing to aggregate over yet. Move the aggregate to HAVING, or compute it in a subquery.

  • ·You wrote something like WHERE COUNT(*) > 5 or WHERE SUM(x) > 100, which belongs in HAVING
  • ·You want to compare a row value to an aggregate of the whole table, which needs a scalar subquery
  • ·The aggregate is hidden inside a CASE, COALESCE, or arithmetic expression inside WHERE
  • ·The aggregate lives in a CTE or derived subquery's WHERE clause and got flagged there
  • ·You meant a window function (ROW_NUMBER, RANK) but wrote an aggregate, which goes in QUALIFY
CHECK FIRSTScan the WHERE for any aggregate function name (COUNT, SUM, AVG, MIN, MAX, ANY_VALUE, ARRAY_AGG, STRING_AGG). If one is there, decide: filter groups after aggregation (HAVING) or compare to a computed value (subquery).

What you're seeing

DuckDB raises this at bind time, before the query runs. The message is identical across the CLI, Python, Node, and JDBC clients.

text
Binder Error: WHERE clause cannot contain aggregates!
LINE 1: SELECT * FROM sales WHERE SUM(amount) > 1000;
                                  ^

# Python client variant:
duckdb.BinderException: Binder Error: WHERE clause cannot contain aggregates!
Also seen as: duckdb where clause cannot contain aggregates, Binder Error: WHERE clause cannot contain aggregates!, duckdb.BinderException: WHERE clause cannot contain aggregates, aggregate in where clause duckdb

What's causing this

Ranked most-likely first.

  1. 1

    An aggregate function is sitting directly in WHERE

    WHERE evaluates row-by-row before GROUP BY runs, so there is no group to aggregate over yet. Any COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, or STRING_AGG in WHERE fails the binder before execution starts.

  2. 2

    You meant HAVING, not WHERE

    HAVING is the filter that runs after GROUP BY, when aggregates exist. A very common typo is writing WHERE where HAVING was intended, especially when the query already has a GROUP BY.

  3. 3

    You want to compare a row to a table-wide aggregate

    Queries like WHERE amount > AVG(amount) look natural but SQL does not support that shape. You need a scalar subquery, a CTE, or a window function so the aggregate is computed separately and then compared.

  4. 4

    The aggregate is buried inside a CASE or arithmetic expression

    WHERE CASE WHEN COUNT(*) > 0 THEN ... trips the same check. The binder walks the whole expression tree, so an aggregate anywhere inside a WHERE predicate fails, not only at the top level.

  5. 5

    You confused an aggregate with a window function

    ROW_NUMBER(), RANK(), and LAG() are window functions, not aggregates. They emit a different error (cannot contain window functions), but the intent is often the same: filter rows by a computed rank. DuckDB has a QUALIFY clause for that.

How to fix it

Step 1: if the query has a GROUP BY, move the aggregate to HAVING

The direct fix when you meant to filter groups. HAVING runs after aggregation, so COUNT, SUM, and friends work there.

sql
-- Wrong:
SELECT customer_id, SUM(amount)
FROM sales
WHERE SUM(amount) > 1000
GROUP BY customer_id;

-- Right:
SELECT customer_id, SUM(amount)
FROM sales
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Step 2: to compare rows to a table-wide aggregate, use a scalar subquery

When you want every row where a column exceeds the average, max, or count of the whole table, put the aggregate in a subquery. The subquery returns one value, WHERE compares against it row-by-row.

sql
-- Wrong:
SELECT * FROM sales WHERE amount > AVG(amount);

-- Right:
SELECT * FROM sales
WHERE amount > (SELECT AVG(amount) FROM sales);

Step 3: for cleaner reuse, precompute in a CTE

If the same aggregate is referenced more than once, a CTE reads better than repeating the subquery and lets the planner materialize the value once.

sql
WITH stats AS (
  SELECT AVG(amount) AS avg_amount,
         MAX(amount) AS max_amount
  FROM sales
)
SELECT s.*
FROM sales s, stats
WHERE s.amount > stats.avg_amount
  AND s.amount < stats.max_amount;

Step 4: for per-group comparisons, use a window function

When each row needs to be compared to an aggregate over its own partition (e.g. per customer, per day), a window function keeps the row grain and computes the aggregate alongside. Wrap the query so the window result is filterable.

sql
-- Rows above their own customer's average:
SELECT *
FROM (
  SELECT *, AVG(amount) OVER (PARTITION BY customer_id) AS cust_avg
  FROM sales
)
WHERE amount > cust_avg;

-- DuckDB shortcut with QUALIFY:
SELECT *, AVG(amount) OVER (PARTITION BY customer_id) AS cust_avg
FROM sales
QUALIFY amount > cust_avg;

Step 5: if you actually meant a window function, use QUALIFY

For row_number, rank, and other window functions, DuckDB supports the QUALIFY clause. It filters rows after window evaluation, the same way HAVING does for GROUP BY.

sql
-- Instead of trying to write:
-- WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ts DESC) = 1

-- Use QUALIFY:
SELECT *
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ts DESC) = 1;

Step 6: check for aggregates hidden inside expressions

The binder walks the whole predicate tree. If the aggregate is nested inside a CASE, COALESCE, or arithmetic operation inside WHERE, extract it into a subquery or CTE.

sql
-- Wrong (aggregate hidden inside CASE):
SELECT *
FROM sales
WHERE CASE WHEN region = 'EU' THEN SUM(amount) ELSE amount END > 100;

-- Right (aggregate lifted into a CTE):
WITH eu_total AS (
  SELECT SUM(amount) AS total FROM sales WHERE region = 'EU'
)
SELECT s.*
FROM sales s, eu_total
WHERE (CASE WHEN s.region = 'EU' THEN eu_total.total ELSE s.amount END) > 100;

Prevention

Learn the SQL clause order early: FROM, WHERE, GROUP BY, HAVING, SELECT, QUALIFY, ORDER BY. WHERE runs before aggregation exists; HAVING and QUALIFY run after. Once the order is internalized, the fix for this error is immediate.

When a query starts with a GROUP BY and then needs to filter results, add a HAVING clause from the start rather than writing WHERE and remembering to move it. Editors and linters often catch the wrong choice at write time.

For row-versus-aggregate comparisons, prefer window functions with QUALIFY over subqueries when the aggregate is per-partition. It reads more clearly and DuckDB executes it in one pass.

Debug DuckDB faster

1bench is a native GUI for DuckDB. Inspect queries, connections, and settings without leaving the app. See what's happening before you have to Google it.

Open DuckDB in 1bench