Post reply

how to use 'group by' with a calculated column

  • July 31, 2003 at 8:32 am

    #80769

    i use a case statement with calculations for one of the columns in the 'select' clause and i want to reference this calculated column in the 'group by' clause. i tried using an alias but apparently that doesn't work. any hint or help at all would be appreciated

  • July 31, 2003 at 8:58 am

    #468011

    I don't think you can do that.

    A work around is doing the calculation in the from clause. Something along the lines of

    SELECT Calc_Field, <other fields>

    FROM (SELECT <other fields>, <Calculated Field> as Calc_field FROM <Your Table>)

    GROUP BY Calc_Field

  • July 31, 2003 at 9:04 am

    #468015

    I'm definitely not certain, but I feel like this can't be done because the expressions that is used in the condition of the case statement would have to be included in the group by statement as well.

    For example:

    case when col1 > 0 then col1 * col2

    else col1 * col3 * -1

    end

    col1 must be accessed for each row that is returned therefore it has to be included in the group by clause.

    So you can't even get close to just including col1*col2 or col1*col3 in the group by clause.

    I think that your best bet would be to nest it:

    select CASEFIELDS, fields from

    (

    select case end as CASEFIELD, other fields from TABLE

    ) as ungrouped

    group by CASEFIELD, fields

    -J

  • July 31, 2003 at 9:45 am

    #468032

    ok, i followed the example as given by JRaha and it worked a treat. I bow humbly before you. Thanks so much.

    kind regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply