Oninit
Talk to an expert →

Informix Error -321

-321 Cannot group by aggregate column.

The GROUP BY clause in this statement refers to a selected value that is an aggregate function. This action is not supported. (It does not make sense to group rows using a value that cannot be computed until the group has been formed.) You can group rows by the value of columns or expressions on columns, but the expressions cannot include aggregate functions. Review the GROUP BY clause, and compare it to the select list. Possibly one of the column numbers in the GROUP BY clause is incorrect.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-321 is a logical impossibility, not just a syntax restriction: GROUP BY can't reference an aggregate function, because grouping has to happen before an aggregate value can be computed — the aggregate is only meaningful once the group already exists. It's the mirror image of -303 (an aggregate in WHERE, evaluated before grouping) applied to GROUP BY itself.

  1. A GROUP BY clause directly naming an aggregate expression from the select list (e.g. GROUP BY COUNT(*)), rather than a plain column.
  2. A GROUP BY referencing a select-list position number that happens to hold an aggregate expression, rather than a plain column — the positional reference resolves to the aggregate.
  3. Confusion between GROUP BY (which defines groups) and HAVING (which filters groups by aggregate value) — an aggregate condition belongs in HAVING, never in GROUP BY.
Solutions / Resolution
  1. Group by column values or column expressions only, per the official guidance — never an aggregate function.
  2. Review the GROUP BY clause against the select list, including verifying any positional column numbers actually point at plain columns, not aggregate expressions.
  3. If the goal is filtering by an aggregate's value, use HAVING instead of GROUP BY.
Examples
An aggregate directly in GROUP BY
SELECT region, COUNT(*) FROM orders
GROUP BY region, COUNT(*);
-- -321: COUNT(*) can't be a grouping key

Fix — group only by the plain column:

SELECT region, COUNT(*) FROM orders
GROUP BY region;
A positional reference resolving to an aggregate
SELECT region, COUNT(*) FROM orders
GROUP BY 1, 2;
-- -321: position 2 is COUNT(*), an aggregate

Fix — reference only the plain column's position:

SELECT region, COUNT(*) FROM orders
GROUP BY 1;
Diagnostic Checks
  1. Check every entry in the GROUP BY clause — by name or position — and confirm none of them resolve to an aggregate expression from the select list.
  2. If filtering by an aggregate value is the actual intent, move that condition to HAVING.
Related Errors / Related Topics
  • -294 — "The column column-name must be in the GROUP BY list." The general GROUP BY-membership rule this restriction sits alongside.
  • -303 — "Expression mixes columns with aggregates." The WHERE-clause counterpart of this same before-grouping/after-grouping distinction.

Grouping keys must be plain columns or column expressions — an aggregate can only be computed after the group already exists, so it can never define one.