How to Fix SQLite "Misuse of Aggregate: count()" (SQL Logic Error 1)

Q: Does ORDER BY COUNT(*) cause this error?

No. ORDER BY runs after grouping, so GROUP BY customer_id ORDER BY COUNT(*) DESC is valid in SQLite, and I ran it to check.

By Pooja Goenka · Published 2026-10-17

SQLite raises "misuse of aggregate: count()" when you put an aggregate function such as COUNT, SUM, MAX or AVG somewhere it cannot be computed yet, and the usual place is a WHERE clause. The fix is to move the condition into HAVING after a GROUP BY, or to compute the aggregate first in a subquery or with a window function and filter on that result.

The "(1)" you sometimes see after the message is SQLite's result code. Code 1 is SQLITE_ERROR, which the result codes page calls "a generic error code" for when nothing more specific applies. Its standard English text is "SQL logic error", which is why some wrappers print "SQL logic error: misuse of aggregate: count() (1)". I checked that by calling sqlite3_errstr(1) directly. The message is the same whether the query came from Android, Python's sqlite3 module or DB Browser for SQLite, because they all hand the SQL to the same library.

Every query below ran in SQLite 3.54.0 through Python's sqlite3 module, against a four-row orders table (customer 1 has two orders, customers 2 and 3 have one each) and a small customers table. The errors are pasted as SQLite printed them. SQLite echoes the function name the way you typed it, so count(*) gives "count()" and COUNT(*) gives "COUNT()".

Why WHERE cannot see COUNT()

A SELECT runs in a fixed order. Rows are read from FROM and JOIN, filtered row by row in WHERE, grouped by GROUP BY, and only then are aggregates computed and tested in HAVING. When WHERE runs, no group exists yet, so there is nothing to count.

The SQLite SELECT documentation spells out the HAVING half of this: the HAVING expression "may refer to values, even aggregate functions, that are not in the result." WHERE gets no such permission.

Cause 1: an aggregate in WHERE

This is the one most people hit. You want customers with more than one order.

SELECT customer_id FROM orders WHERE COUNT(*) > 1 GROUP BY customer_id;

sqlite3.OperationalError: misuse of aggregate: COUNT()

Move the condition to HAVING:

SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- [(1, 2)]

You may also see a slightly different wording, with the same cause. When the query has no GROUP BY, SQLite 3.54 says "misuse of aggregate function" instead of "misuse of aggregate:".

SELECT * FROM orders WHERE COUNT(*) > 1;

sqlite3.OperationalError: misuse of aggregate function COUNT()

The same error with sum(), max() and avg()

COUNT is only the most searched version. Any aggregate in WHERE fails the same way:

SELECT customer_id FROM orders WHERE SUM(amount) > 100 GROUP BY customer_id;
-- misuse of aggregate: SUM()

SELECT * FROM orders WHERE amount = MAX(amount);
-- misuse of aggregate function MAX()

SELECT * FROM orders WHERE amount > AVG(amount);
-- misuse of aggregate function AVG()

The last two are trying to compare each row with a figure for the whole table, which HAVING cannot help with. Compute the figure in a subquery first:

SELECT id, amount FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
-- [(2, 100), (3, 200)]

SELECT id, amount FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);
-- [(3, 200)]

Cause 2: one aggregate nested inside another

People write COUNT(MAX(x)) or SUM(COUNT(*)) when they want "how many groups" or "a total of the per-group counts".

SELECT COUNT(MAX(amount)) FROM orders GROUP BY customer_id;

sqlite3.OperationalError: misuse of aggregate function MAX()

SQLite blames the inner function, which can be confusing when you were looking at the outer one. The fix is two levels of query. Aggregate per group in a subquery, then aggregate the subquery's rows:

SELECT COUNT(*) FROM (
  SELECT customer_id, MAX(amount) AS m FROM orders GROUP BY customer_id
);
-- [(3,)]

Cause 3: an aggregate in GROUP BY

Grouping by a count, for example to ask "how many customers placed one order, how many placed two", gets its own message:

SELECT COUNT(*) FROM orders GROUP BY COUNT(*);

sqlite3.OperationalError: aggregate functions are not allowed in the GROUP BY clause

The SELECT docs say it directly: "The expressions in a GROUP BY clause may not be aggregate expressions." Count per customer in a subquery, then group by that count:

SELECT n, COUNT(*) AS customers FROM (
  SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
) GROUP BY n;
-- [(1, 2), (2, 1)]

Two customers have one order and one customer has two.

Cause 4: an aggregate inside a JOIN ON condition

An ON condition is checked pair by pair while rows are being matched, which is before any grouping, so it has the same problem as WHERE.

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND COUNT(o.id) > 1
GROUP BY c.name;

sqlite3.OperationalError: misuse of aggregate: COUNT()

Keep the join condition about matching rows and put the count test in HAVING:

SELECT c.name, COUNT(o.id) FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 1;
-- [('Asha', 2)]

When you need a total on every row, use a window function

Sometimes what you want is the total next to each row, for a "row 3 of 40" label or a percentage of the whole. GROUP BY collapses the rows, which is the wrong shape. A window function keeps every row and adds the aggregate as a column:

SELECT id, amount, COUNT(*) OVER () AS total_rows FROM orders;
-- [(1, 50, 4), (2, 100, 4), (3, 200, 4), (4, 30, 4)]

SELECT id, amount, ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct
FROM orders;
-- [(1, 50, 13.2), (2, 100, 26.3), (3, 200, 52.6), (4, 30, 7.9)]

COUNT(*) OVER (PARTITION BY customer_id) gives a per-customer count on each row instead. Window functions need SQLite 3.25 or later.

One trap: a window function is not allowed in WHERE either. The window functions page says they "may only appear in the result set and in the ORDER BY clause". Try it and you get a close cousin of our error:

SELECT id FROM orders WHERE COUNT(*) OVER () > 1;

sqlite3.OperationalError: misuse of window function COUNT()

To filter on a window result, compute it in a subquery and filter in the outer query.

What PostgreSQL and MySQL say for the same mistakes

I ran the four broken queries above on PostgreSQL 16. Its messages name the clause, which I find more useful than SQLite's:

ERROR:  aggregate functions are not allowed in WHERE
ERROR:  aggregate function calls cannot be nested
ERROR:  aggregate functions are not allowed in GROUP BY
ERROR:  aggregate functions are not allowed in JOIN conditions

MySQL reports these mistakes as error 1111, ER_INVALID_GROUP_FUNC_USE, whose text in the MySQL server error reference is "Invalid use of group function". I did not have a MySQL server to run each query on, so I have not confirmed which of the four produce 1111 and which raise a different error.

The fixes are identical in all three databases, because the cause is the order in which SQL evaluates a query, which every engine shares.

Where this turns up in interviews

"Why can't I use COUNT in WHERE?" is a standard SQL screening question, and "WHERE SUM(amount) > 100" is the first wrong answer in my post on SQL interview answers that sound right and are wrong. That post covers seven more, including NULL comparisons and COUNT(*) against COUNT(column).

If you want to practise catching mistakes like these in someone else's answer, LogicWiz Arena is a free game where an AI interviewer gives an answer and you spot what is wrong with it.

FAQ

What does "(1)" mean in "sql logic error: misuse of aggregate: count() (1)"?

It is SQLite's result code. 1 is SQLITE_ERROR, the generic code for errors such as bad SQL, and "SQL logic error" is the library's standard description of that code. The part that tells you what went wrong is "misuse of aggregate: count()".

Can I use COUNT() in a WHERE clause in SQLite?

Not directly. Use HAVING after GROUP BY to filter groups, or put the COUNT in a subquery, such as WHERE x > (SELECT COUNT(*) FROM t), to compare rows with a single total.

Why does the error mention MAX() when my query fails on COUNT(MAX(x))?

SQLite reports the inner aggregate, the one that cannot be evaluated where it sits. Move the inner aggregate into a subquery and apply the outer one to its result.

Is "misuse of aggregate function count()" a different error?

No. SQLite 3.54 prints "misuse of aggregate function" when the query has no GROUP BY and "misuse of aggregate:" when it does. Both come from an aggregate in a clause that runs before grouping, and the same fixes apply.

Does ORDER BY COUNT(*) cause this error?

No. ORDER BY runs after grouping, so GROUP BY customer_id ORDER BY COUNT(*) DESC is valid in SQLite, and I ran it to check.