Refer to a calculated column in query to calclulate another column

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  September 13, 2011 at 1:09 pm

  #257341

  I want to calculate a column for a query as a function of table columns, say:

  Select ColA*ColB*ColC as ColMultiple from tblCustomer....

  Now, I'd like to calculate another column as a function of the first calculated column, say:

  Select ColD/ColMultiple as ColAverage from tblCustomer....

  Must I repeat the calculation of 'ColMultiple' each time I want to use that result, or is there a way to refer to the newly-calculated colum, say:

  Select ColA*ColB*ColC as ColMultiple, ColD/ColMultiple as ColAverage from tblCustomer....

  I know that doesn't work... is there a way to do it?

  Jim

• drew.allen

  SSC Guru

  Points: 76998

  More actions

  September 13, 2011 at 1:17 pm

  #1380894

  You can use a CROSS APPLY.

  SELECT ColMultiple, ColD/ColMultiple as ColAverage

  FROM tblCustomer

  CROSS APPLY (

  SELECT ColA*ColB*ColC as ColMultiple

  ) AS cm

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA