Executing Calculation on an Column Alias

Transact-SQL
1

Hello Community,

Can someone let me know if its possible to execute calculations on a column alias.

I know its possible with SAS, see below:

Referring to a Calculated Column by Alias
When you use a column alias to refer to a calculated value, you must use the CALCULATED keyword with the alias to inform PROC SQL that the value is calculated within the query. The following example uses two calculated values, LowC and HighC, to calculate a third value, Range:
libname sql 'SAS-library';

proc sql outobs=12;
title 'Range of High and Low Temperatures in Celsius';
select City, (AvgHigh - 32) * 5/9 as HighC format=5.1,
(AvgLow - 32) * 5/9 as LowC format=5.1,
(calculated HighC - calculated LowC)
as Range format=4.1
from sql.worldtemps;

As you can see from above SAS code, it is possible to execute a query on an alias using the CALCULATED keyword.

Is there an equivalent with T-SQL?

Thanks

2

Not sure if this is what you're seeking.
In MSSQL you can have computed column on a table: https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017

3

Hi bitsmed,

I don't think that is going to work in this case

4

Then maybe thru subselect:

select city
      ,highc
      ,lowc
      ,highc-lowc as range
  from (select city
              ,(avghigh-32)*5/9 as highc
              ,(avglow-32)*5/9 as lowc
          from sql.worldtemps
       ) as a
;

or simply

select city
      ,(avghigh-32)*5/9 as highc
      ,(avglow-32)*5/9 as lowc
      ,((avghigh-32)*5/9)
      -((avglow-32)*5/9)
       as range
  from sql.worldtemps
;
1 Like
5

Ahhhhhhh .... I see. Once again, thanks bitsmed

6

You can also use CROSS APPLY...

Cross Apply (Values ((avghigh - 32) * 5 / 9, (avglow - 32) * 5 / 9)) As a(highc, lowc)

You then have:

Select ..., a.highc - a.lowc as range
From ...

1 Like
7

Jeff,

This is something that is a little advanced for me :slightly_smiling_face: However, I will make it work.

Thank you