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.
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.
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.
CTEfor my example? — May 30, 2012 at 14:49