Database Administration
sql-server sql-server-2008 query aggregate syntax
Updated Mon, 15 Aug 2022 03:18:21 GMT

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


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.




Solution

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.





Comments (3)

  • +0 – there is no performance improvement using a CTE for my example? — May 30, 2012 at 14:49  
  • +0 – @RubensMariuzzo I think it's still going to be calculated only once. — May 30, 2012 at 14:50  
  • +1 – @RubensMariuzzo - No. Your original query will perform just as well as the CTE I proposed. In this case, the CTE just saves you from retyping your calculation. — May 30, 2012 at 14:53