Calling Inline Table Valued Function inside AGGREGATE function (SUM,MAX etc..)

Question

Hi all,

I have TABLE VALUED function that's called in each row.

This is working fine if i call as following.

select AMOUNT * (select QTY from dbo.GET_Order_Qty(Order_Id)) FROM ORDER_DETAILS

But I want to apply aggregate on this as following which is not working.

select SUM(AMOUNT * (select QTY from dbo.GET_Order_Qty(Order_Id))) FROM ORDER_DETAILS

This says SELECT can't be called inside aggregate function. And if i convert this Table Valued Function again into scalar function and performance drops significantly.

Any thoughts please ?

Thanks in advance

 

 

 

 

Answer 1

select sum ( vol )
from
(
select AMOUNT * (select QTY from dbo.GET_Order_Qty(Order_Id)) as vol FROM ORDER_DETAILS
) t

---

http://www.t-sql.ru

Answer 2

Just guessing (sorry not tested)

SELECT SUM(col) FROM

(

select AMOUNT * QTY  AS col  FROM ORDER_DETAILS

CROSS APPLY

(select QTY from dbo.GET_Order_Qty(Order_Id)) as der

) AS D

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Try to bind your initial query in CTE or derived-sub query. And then apply SUM() aggregate to it, like:

 

;with cte as (select AMOUNT * (select QTY from dbo.GET_Order_Qty(Order_Id)) as col FROM ORDER_DETAILS)
select sum(col) from cte

... I've not tested this, plz check and let us know.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 4

select sum ( t1.AMOUNT * t2.Qty) FROM ORDER_DETAILS t1
cross apply dbo.GET_Order_Qty(t1.Order_Id) t2 

---

http://www.t-sql.ru

Answer 5

 

Hi all,

Thanks all for your reply.

I went with CROSS APPLY in terms of performance and works well.

Thanks again

 

 

 

---

soori