Oninit
Talk to an expert →

Informix Error -294

-294 The column column-name must be in the GROUP BY list.

In a grouping SELECT, you must list every nonaggregate column in the GROUP BY clause to ensure that a well-defined value exists for each selected column in each grouped row. A column contains either a single aggregate value or a value unique to that group. If a selected column were neither an aggregate nor in the list, two or more values for that column might possibly exist in some group, and the database server could not choose which value to display. Revise the query to include either the column name or its positional number in the clause.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-294 is one of the most fundamental and commonly hit GROUP BY errors: every non-aggregate column in the select list must also appear in the GROUP BY clause, because the engine has no way to know which value to display for that column when multiple rows (and potentially multiple distinct values) exist within a single group.

  1. A select list column that isn't wrapped in an aggregate function and isn't listed in GROUP BY — the direct cause.
  2. Adding a new column to the select list without also adding it to GROUP BY (or wrapping it in an aggregate) — very common when iteratively extending a report query over time.
  3. Assuming a column is "effectively" the same within a group (functionally dependent on the grouped column) without telling the engine explicitly via GROUP BY — the engine doesn't infer this relationship on its own.
  4. Confusing which columns define groups versus which should be summarized within groups, in a query mixing detail and summary information.
Solutions / Resolution
  1. Add the column to the GROUP BY clause (by name or positional number), per the official guidance, if its value is genuinely meant to be distinct per group.
  2. Wrap the column in an appropriate aggregate function (MAX, MIN, and similar) if a single representative value — rather than a grouping key — is what's actually needed.
  3. Review the query's intent: does this column define groups, or does it need summarizing within groups? Choose GROUP BY or an aggregate function accordingly, rather than defaulting to one without thinking it through.
Examples
The straightforward fix: add to GROUP BY
SELECT region, customer_name, COUNT(*) FROM orders GROUP BY region;
-- -294: customer_name is neither aggregated nor grouped

Fix — if customer_name should define its own grouping level:

SELECT region, customer_name, COUNT(*) FROM orders GROUP BY region, customer_name;
The alternative fix: aggregate instead
SELECT region, customer_name, COUNT(*) FROM orders GROUP BY region;
-- -294

Fix — if only a representative value is needed, not a separate grouping level:

SELECT region, MAX(customer_name), COUNT(*) FROM orders GROUP BY region;
A column added later without updating GROUP BY
-- Originally:
SELECT region, COUNT(*) FROM orders GROUP BY region;

-- Later, a column was added to the select list without updating GROUP BY:
SELECT region, order_date, COUNT(*) FROM orders GROUP BY region;
-- -294: order_date needs to be in GROUP BY or wrapped in an aggregate
Diagnostic Checks
  1. Review each column in the select list against the GROUP BY clause — confirm each is either aggregated or grouped, with no exceptions.
Related Errors / Related Topics
  • -251 — "ORDER BY or GROUP BY column number is too big." Another GROUP BY-related condition, though about positional numbering rather than the aggregate/group requirement.
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Decide the column's role first — grouping key or summarized value — then either add it to GROUP BY or wrap it in an aggregate function accordingly.