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;



  • Edited by xxjergerxx Tuesday, May 26, 2015 2:50 AM
May 26th, 2015 2:48am

celko: thanks for feedback, I tried several variations and kept receiving errors that ordertotal was invalid or that there were not enough columns.

Eralper, its kind of what I was learning a couple days ago. In this case its stored in a temporary table or is this a permanent table? One odd thing is it keeps rounding up, I tried decimal. Any explanation?

results

20006 55
20008 125
20005 150
20007 1000

it should be:

20006 55.00
20008 125.00
20005 149.87
20007 1000.00

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 8:44pm

this helped fix the decimals, any advise on how to make it dynamic without (10,2)? Or is that the common principal with variables in sql? Its odd that the select without this method without using a variable just displays the result. 

declare @t as table (
order_num decimal (10,2),
ordertotal decimal (10,2)
)

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 8:45pm

yes, you would need to specify decimal data types if you need decimal data. 

the reason you were not getting decimals is because - the data types were defined as int. and int does not have decimals.. so, should define datatype as decimal and whether it is decimal (10,2) or decimal(10,3) depends on how many zeros you need after . 2 refers to two zeros on the right side of '.'  3 refers to 3 zeros on the right side of '.'

and 10 refers to total no of digits in the data.

yes, in the eralper code, he is inserting the data into table variable- in short, this is temporary table.

the reason he is doing into a temp rather than returning the value directly is because in sql , a variable can only store one value but if you have bunch of values being returned you store them in a table. if your query returns only one row, you can use direct variable and return the values..

Celko code should also work for you. he has typo in the where clause it should be order_total and not ortder_total. try running again after fixing the this  

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 9:29pm

Thanks for explanation. I still have issues with Celko code:

-also table is "orderitems"

Msg 208, Level 16, State 1, Line 1
Invalid object name 'X.order_total'.

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

SELECT X.order_num, X.order_total
  FROM X.order_total
 WHERE X.orter_total >= 50;

May 26th, 2015 10:09pm

Thanks for explanation. I still have issues with Celko code:

-also table is "orderitems"

Msg 208, Level 16, State 1, Line 1
Invalid object name 'X.order_total'.

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

SELECT X.order_num, X.order_total
  FROM X.order_total
 WHERE X.orter_total >= 50;

try this..

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

SELECT order_num, order_total
  FROM X
 WHERE order_total >= 50;

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 10:25pm

Thanks for the feedback. I'm struggling using that in a larger code. I'm not quiet sure how to integrate it to something like. I'm stuck on select, group by without having ambigous names. I'll have to revisit once I understand how to use it more! :) 

select customers.cust_city, customers.cust_id, customers.cust_name, orderitems.quantity, products.prod_id, products.prod_price, SUM(orderitems.quantity*orderitems.item_price) AS ordertotal
FROM customers, orders, orderitems, products
WHERE customers.cust_id=orders.cust_id
    and orderitems.order_num = orders.order_num
    and orderitems.prod_id = products.prod_id
  --and orderitems.prod_id='TNT2'

GROUP BY cust_name,customers.cust_city, customers.cust_id, products.prod_id, orderitems.quantity, products.prod_price;

May 27th, 2015 8:53pm

Thanks for the feedback. I'm struggling using that in a larger code. I'm not quiet sure how to integrate it to something like. I'm stuck on select, group by without having ambigous names. I'll have to revisit once I understand how to use it more! :) 

select customers.cust_city, customers.cust_id, customers.cust_name, orderitems.quantity, products.prod_id, products.prod_price, SUM(orderitems.quantity*orderitems.item_price) AS ordertotal
FROM customers, orders, orderitems, products
WHERE customers.cust_id=orders.cust_id
    and orderitems.order_num = orders.order_num
    and orderitems.prod_id = products.prod_id
  --and orderitems.prod_id='TNT2'

GROUP BY cust_name,customers.cust_city, customers.cust_id, products.prod_id, orderitems.quantity, products.prod_price;

ambigous names are common when you have same column in different tables. in such you have call it with either table name or Alias name....
Free Windows Admin Tool Kit Click here and download it now
May 27th, 2015 10:12pm

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

Other recent topics Other recent topics