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

  • 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

     

     

     

     

    Tuesday, July 19, 2011 1:02 PM

Answers

  • 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
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 4:53 PM
    Tuesday, July 19, 2011 1:09 PM

All replies

  • 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
    Tuesday, July 19, 2011 1:05 PM
  • 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/
    Tuesday, July 19, 2011 1:07 PM
    Answerer
  • 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
    Tuesday, July 19, 2011 1:08 PM
  • 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
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 4:53 PM
    Tuesday, July 19, 2011 1:09 PM
  •  

    Hi all,

    Thanks all for your reply.

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

    Thanks again

     

     

     


    soori
    Tuesday, July 19, 2011 3:09 PM