Oninit
Talk to an expert →

Informix Error -367

-367 Sums and averages cannot be computed for character columns.

This statement contains a use of the SUM or AVG function applied to a column that has a character data type (CHAR or VARCHAR). If you did not intend to take the sum or average of character strings, review the spelling of column names against the table definition. If a character column actually contains numeric values in character form, you can trick the database server (Version 4.0 and later) into performing an automatic conversion. Instead of applying the function to the column name alone, apply it to the expression in parentheses (column+0).

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-367 fires when SUM or AVG is applied to a CHAR/VARCHAR column — these aggregate functions are only meaningful for numeric types, and Informix doesn't implicitly convert character data for this purpose the way it does in some other contexts.

  1. Applying SUM/AVG to a genuinely non-numeric character column — a naming or logic mistake, since character data has no numeric total or average.
  2. A numeric value stored in a character column (a common legacy or import-driven schema choice), where the intent was always numeric but the column's actual type is CHAR/VARCHAR.
  3. Wrong column referenced — a similarly-named numeric column exists, but the aggregate was applied to the character column by mistake.
Solutions / Resolution
  1. Verify the column's actual data type against the table definition, per the official guidance, in case the wrong column was referenced.
  2. If the character column genuinely holds numeric values, force a conversion with an expression like (column+0), per the official guidance — this triggers Informix's automatic type conversion (supported from Version 4.0 onward) so SUM/AVG can operate on the result.
  3. For a longer-term fix, consider migrating the column to an actual numeric type if it consistently holds numeric data, rather than relying on the +0 conversion trick indefinitely.
Examples
Forcing numeric conversion on a character column
SELECT SUM(amount) FROM legacy_transactions;
-- -367: amount is CHAR/VARCHAR, even though it holds numeric text

SELECT SUM(amount + 0) FROM legacy_transactions;
-- works: (amount + 0) forces conversion to a numeric type
Confirming the intended column's actual type
SELECT colname, coltype FROM syscolumns
WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'legacy_transactions');
Diagnostic Checks
  1. Check the target column's actual declared type via syscolumns — confirm it's CHAR/VARCHAR rather than a numeric type.
  2. Confirm whether the column's stored values are genuinely numeric text, if considering the +0 conversion workaround.
Related Errors / Related Topics
  • -352 — "Column column-name not found." A related column-reference issue, though about a missing column rather than an aggregate-function type mismatch.

(column + 0) is the standard workaround for a numeric-valued character column, but consider migrating to an actual numeric type if this comes up repeatedly on the same column.