Using alias in the same select

Question

Once I set an a column name like this SUM(someColumn) AS [ColumnName], how can I use it afterwards ?

Let's say I build a query like this :

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       -- I need [TotalFooBar]

And in the same select I want the total of [TotalFoo] and [TotalBar]. Is it possible to refere to these columns ? I could do it the long way :

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       SUM((foo.A + foo.B + foo.C + foo.D + foo.E) +
           (bar.A + bar.B + bar.C + bar.D + bar.E)) AS [TotalFooBar]

It's ok in this exemple but I have way more columns and it's hard to follow. And then what if I have to use this column ([TotalFooBar])? I'll have to rewrite the whole SUM everytime ?
What I'm looking for is something like this :

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       SUM([TotalFoo] + [TotalBar]) AS [TotalFooBar]

Is it even possible ?

Answer 1

No, you can't do this in the same SELECT statement, but you can use subquery:

SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM
(
    SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
           SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
           ...
    FROM ...
) AS sub
...

or a CTE:

WITH CTE
AS
(
   SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
          SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
          ...
   FROM ...
)
SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM CTE