how to turn select aggregate function statement into a variable?

Hello, I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place? Can someone help show me an example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I had hoped to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

Thanks for the coaching! :)  I am studying a "sql 2005 in 10min" book and practicing in sql express 2014. This is for my own learning so no rush thanks!

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;



May 25th, 2015 10:50pm

You can use a CTE, but in this case I am not sure it is worth it. The optimizer should look for common expressions that can be factored out.  


WITH X
 AS
(SELECT order_num, SUM(quantity*item_price) AS order_total
FROM Order_items
GROUP BY order_num)

SELECT X.order_num, X.order_total
  FROM X.order_total
 WHERE X.ortder_total >= 50;
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 11:28pm

You can use a table variable and use it repeatedly unless a GO statement is executed

Here is a sample

declare @t as table (
	order_num int,
	ordertotal int
)

insert into @t
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50

select * from @t order by ordertotal;

May 26th, 2015 1:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics