CODEWITHSUNDEEP
sqlsql-server
Amresh Kumar Singh
Amresh Kumar Singh

Reputation: 133

Sum of SQL Function return value

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

Upvotes: 0

Views: 53

Answers (2)

Tedo G.
Tedo G.

Reputation: 1565

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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

Upvotes: 4

Related Questions