Sum of SQL Function return value

Question

I have a SQL Query:

Select Top 3 dbo.FN_GetLRExpenseAmount(VendorBillID,LRNo,PM.PickupRunsheetNo)
From PickupRunsheetMaster PM Left Outer Join
     PickupRunsheetDetail PRD
     ON PM.PickupRunsheetNo = PRD.PickupRunsheetNo
Where ISNULL(VendorBillID,0) > 0

Output:

601.90
14.56
145.62

When I sum the return value of function dbo.FN_GetLRExpenseAmount(VendorBillID,LRNo,PM.PickupRunsheetNo) throuth below query then it does not return any value

Select Top 3 SUM(dbo.FN_GetLRExpenseAmount(VendorBillID, LRNo, PM.PickupRunsheetNo))
From PickupRunsheetMaster PM Left Outer Join
     PickupRunsheetDetail PRD
     ON PM.PickupRunsheetNo = PRD.PickupRunsheetNo
Where ISNULL(VendorBillID,0) > 0

Gordon Linoff · Accepted Answer · 2016-09-14 11:27:13Z

4

The queries are not the same. The TOP 3 does nothing in the second query, because it is an aggregation query that returns only one row.

For an equivalent query, use a subquery or CTE:

select sum(val) as sum_3
from (Select Top 3 dbo.FN_GetLRExpenseAmount(VendorBillID, LRNo, PM.PickupRunsheetNo) as val
      From PickupRunsheetMaster PM Left Outer Join
           PickupRunsheetDetail PRD
           ON PM.PickupRunsheetNo = PRD.PickupRunsheetNo
      Where ISNULL(VendorBillID,0) > 0
     ) x

answered Sep 14, 2016 at 11:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@AmreshKumarSingh . . . When using TOP you should also use ORDER BY. The subquery (and your original query) return three arbitrary matching rows.

Tedo G. · Accepted Answer · 2016-09-14 11:28:23Z

1

TOP 3 SUM gives you this result. To get the sum value of the top 3 records you want, Create a subquery:

SELECT
     SUM(A.Expense)
FROM
(
    Select Top 3 dbo.FN_GetLRExpenseAmount(VendorBillID, LRNo, PM.PickupRunsheetNo) AS [Expense]
    From PickupRunsheetMaster PM Left Outer Join
         PickupRunsheetDetail PRD
         ON PM.PickupRunsheetNo = PRD.PickupRunsheetNo
    Where ISNULL(VendorBillID,0) > 0
) AS A

answered Sep 14, 2016 at 11:28

Tedo G.

1,5653 gold badges18 silver badges32 bronze badges

Collectives™ on Stack Overflow

Sum of SQL Function return value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related