Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: informix SQL Group By

    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

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •