Informix Error -231: Cannot perform aggregate function with distinct on expression.

Reasons / Common Causes

-231 is a specific, well-defined restriction with a documented workaround: DISTINCT inside an aggregate function only works against a simple column reference, not a computed expression.

1. DISTINCT applied to a complex expression inside an aggregate function — COUNT(DISTINCT col1 + col2) or SUM(DISTINCT some_expr), rather than a plain column reference.
2. Assuming DISTINCT works the same way on expressions as on simple columns within an aggregate — a reasonable assumption that just isn't supported here.
3. SQL ported from another database system that permits DISTINCT on arbitrary expressions inside aggregates directly.

Solutions / Resolution

1. Select the DISTINCT expression into a temporary table first, then apply the aggregate function with ALL against that table — the documented workaround, per the official guidance.
2. Alternatively, use a subquery or derived table to perform the DISTINCT step separately before aggregating, functionally equivalent to the temp-table approach.

Examples

The disallowed pattern

SELECT COUNT(DISTINCT price * quantity) FROM order_items;
-- -231: DISTINCT on a computed expression inside an aggregate

The documented workaround

SELECT DISTINCT price * quantity AS line_total INTO TEMP tmp_totals
FROM order_items;

SELECT COUNT(*) FROM tmp_totals;

Using a derived table instead

SELECT COUNT(*) FROM (
  SELECT DISTINCT price * quantity AS line_total FROM order_items
) t;

Diagnostic Checks

1. Review the aggregate function for a DISTINCT applied to something other than a simple column reference — that's always the trigger for this specific error.

Related Errors / Related Topics

• -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Compute the expression first (into a temp table or a derived table), then apply DISTINCT and the aggregate separately — that's the standard, documented pattern for this restriction.