Hi, as I understand it Informix SQL does not support grouping by an aliased column, the query below will throw an error.

SELECT acct_id
, CASE WHEN (some case statement) AS aliased_C
, SUM(amount) AS Amount_S
FROM some.table
GROUP BY acct_id
, aliased_C

In the environment I am using (SQL Passthrough via SAS) including the entire case statement in the group by also throws an error (syntax).
But, changing the group by to reference the column as numeric (below) seems to produce the right answers in my test query.

This seems to go against the rationale for not being able to group by an alias as per my understanding of what order a sql query gets executed.

So my question is, Is grouping by a column number an accepted practice or are my results an anomoly that can't or shouldn't be relied upon?

SELECT acct_id
, CASE WHEN (some case statement) AS aliased_C
, SUM(amount) AS Amount_S
FROM some.table
GROUP BY acct_id
, 2

Hi,

it's perfectly OK to group by on column numbers in Informix (as well as order by). The only difference with grouping by the actual column names is that using numbers will group the results of the query where using numbers will group the contents of the databasecolumn. In most cases the results are the same, as in this case.

Regards,
Hans