Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

Question

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query:

SELECT 
  SUM(Price * Quantity) as 'Total',
  SUM(Price * Quantity) * 0.95,
FROM
  SomeTables;

To something like this:

SELECT
  SUM(Price * Quantity) as 'Total',
  'Total' * 0.95,
FROM
  SomeTables;

Note: This is just a sample to clarify/support my question. My current SQL query is larger and more complex than this.

score 3 · Accepted Answer · answered May 30 '12 at 14:42

Nope. Only your ORDER BY clause can reference assigned aliases in the same query.

I suggest declaring a CTE that computes the first value, and then computing the second value in a query against that CTE.

For example:

WITH totals AS (
   SELECT SUM(Price * Quantity) AS Total
   FROM   SomeTable
)
SELECT 
     Total
   , (Total * 0.95) AS DiscountedTotal
FROM totals;

Think of a CTE as an inline, disposable view. It is valid only for the query that immediately follows it. In that regard, it doesn't give you any performance benefit over doing the same thing with a derived table or with an actual view, or over computing the total twice like in your original query.

Of course, using a CTE does have an advantage over calculating the totals twice in two different queries, and it does look cleaner than all the other approaches.

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

1 Answers1