Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
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. –  Nick Chammas May 29 '12 at 22:07
    
By CTE do you mean Common Table Expression? –  Rubens Mariuzzo May 29 '12 at 22:12
1  
Yes, like this. –  Nick Chammas May 29 '12 at 22:14
1  
@NickChammas - apart from improved readability and maintenance, does CTE improve performance as well in a query like this (on SqlServer)? In PostgreSQL, the aggregate is computed only once. –  dezso May 30 '12 at 7:20
1  
@dezso - Nope. CTEs are mainly used to improve or enable readability, code reuse, and recursive queries. –  Nick Chammas May 30 '12 at 14:44
show 1 more comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
there is no performance improvement using a CTE for my example? –  Rubens Mariuzzo May 30 '12 at 14:49
    
@RubensMariuzzo I think it's still going to be calculated only once. –  JNK May 30 '12 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. –  Nick Chammas May 30 '12 at 14:53
    
Thanks @NickChammas, I'll keep this in mind. –  Rubens Mariuzzo May 31 '12 at 15:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.