Why does my Scalar UDF funciton return more than one row?

Question

I have a scalar UDF function that should return one row when called. It actually returns the same value for each row in the table, 27 times. I can easily add a 'Distinct' or 'Top 1' when I call the function but I shouldn't have to.

Here is the function code:

CREATE FUNCTION dbo.TotalIncome(@name AS VARCHAR(10), @weekday AS VARCHAR(10))
RETURNS INT
AS
BEGIN
    DECLARE @total int;
    SELECT @total = SUM(IncomeAmount)
    FROM DailyIncome
    WHERE VendorName = @name
        AND EachDay = @weekday;
    IF (@total IS NULL)
        SET @total = 0;
    RETURN @total;
END;

Here is the code where I call the function:

SELECT dbo.TotalIncome('SPIKE', 'FRI') AS TotalIncome
FROM dbo.DailyIncome

It should return one row but it returns 27.

Answer 1

It's because you have 27 rows in dbo.DailyIncome. The UDF is called once for each row. Maybe you want something like this instead:

SELECT dbo.TotalIncome('SPIKE', 'FRI') AS TotalIncome