CODEWITHSUNDEEP
sql-server
Zak Meyer
Zak Meyer

Reputation: 53

SQL Server : user-defined function that returns the sum of all the values corresponding to an employeeID

I am trying to use a scalar function that takes an employeeID in as a parameter and returns the total sales amount made by that employee.

Unfortunately, my code keeps returning the total but multiple times, once for each instance of employeeID in the table. How would I make my function return a single value?

ALTER FUNCTION dbo.ufnEmployeeRevenue (@employeeID INT)
RETURNS MONEY
AS
BEGIN
    DECLARE @total MONEY

    SELECT DISTINCT @total = SUM(S.Total)
    FROM tblSale S
    WHERE S.EmployeeID = @employeeID

    RETURN @total
END

SELECT dbo.ufnEmployeeRevenue(EmployeeID) 
FROM tblSale
WHERE EmployeeID = 1

[enter image description here][1]

Output

Upvotes: 0

Views: 2491

Answers (2)

Sean Lange
Sean Lange

Reputation: 33580

I would suggest turning that scalar function into an inline table valued function instead. It is faster than a scalar function and far more flexible. Here is the same logic as an inline table valued function.

ALTER FUNCTION dbo.ufnEmployeeRevenue (@employeeID INT)
RETURNS TABLE
AS
    RETURN 
    SELECT TotalMoney = SUM(S.Total)
    FROM tblSale S
    WHERE S.EmployeeID = @employeeID

GO

SELECT m.TotalMoney
FROM tblSale s
cross apply dbo.ufnEmployeeRevenue(EmployeeID) m
WHERE EmployeeID = 1

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Your call is wrong, you just need to pass the ID number:

select dbo.ufnEmployeeRevenue(1);

When you pass 1 (or any id number) to the function, it will run the code inside it as:

SELECT DISTINCT @total = SUM(S.Total)
    FROM tblSale S
    WHERE S.EmployeeID = 1 --@employeeID

Finally, it will return the final result for you.

Details about functions can be found in the Create User-defined Functions, which include examples.

Upvotes: 2

Related Questions