Referencing multiple aliases in a new field

Question

I'm calculating 3 new fields where calculation #2 is dependent on calculation #1 and calculation #2 is dependent on calculation #3.

I'd like to alias these calculations to create a cleaner solution, but I'm not sure how to reference more than one alias. If I just had 2 calculations, I know I could create a subquery and reference my alias in the upper level. However, I'm not sure how to do this with 3 calculations. Would I join subqueries?

Reprex below (current code will encounter an error when attempting to reference an alias within an alias.)

DECLARE @myTable AS TABLE([state] VARCHAR(20), [season] VARCHAR(20), [rain] int, [snow] int, [ice] int)
INSERT INTO @myTable VALUES ('AL', 'summer', 1, 1, 1)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 3, 1)
INSERT INTO @myTable VALUES ('AZ', 'summer', 0, 1, 1)
INSERT INTO @myTable VALUES ('AL', 'winter', 5, 4, 2)
INSERT INTO @myTable VALUES ('AK', 'winter', 2, 2, 2)
INSERT INTO @myTable VALUES ('AZ', 'winter', 1, 1, 2)
INSERT INTO @myTable VALUES ('AL', 'summer', 6, 4, 3)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 0, 3)
INSERT INTO @myTable VALUES ('AZ', 'summer', 5, 1, 3)

select *,
       ice + snow as cold_precipitation,
       rain as warm_precipitation, 
       cold_precipitation + warm_precipitation as overall_precipitation,
       cold_precipitation / sum(overall_precipitation) as cold_pct_of_total,
       warm_precipitation / sum(overall_precipitation) as warm_pct_of_total
from @myTable

John Cappelletti · Accepted Answer · 2022-10-13T21:53:27.893

You can use CROSS APPLY(s) to stack expressions and reference aliases. However, you have an aggregate sum() without a GROUP BY, so your desired results is not clear.

I did change your sum() to a window function sum() over(partition by state,season)

Example

select A.*,
       cold_precipitation,
       warm_precipitation, 
       overall_precipitation,
       cold_precipitation / sum(overall_precipitation+0.0) over(partition by state,season) as cold_pct_of_total,
       warm_precipitation / sum(overall_precipitation+0.0) over(partition by state,season) as warm_pct_of_total
from @myTable A
Cross Apply ( values ( ice + snow , rain ) ) B(cold_precipitation,warm_precipitation)
Cross Apply ( values ( cold_precipitation+warm_precipitation )) C(overall_precipitation)

Results

Referencing multiple aliases in a new field

1 Answers1