SQL UDF Group By Parameter Issue

Question

I'm having some issues with a group by clause in SQL. I have the following basic function:

CREATE FUNCTION dbo.fn_GetWinsYear (@Year int)
RETURNS int
AS
BEGIN
  declare @W int
  select @W = count(1) 
    from tblGames 
    where WinLossForfeit = 'W' and datepart(yyyy,Date) = @Year
  return @W
END

I'm trying to run the following basic query:

select dbo.fn_GetWinsYear(datepart(yyyy,date)) 
from tblGames 
group by datepart(yyyy,date)

However, I'm encountering the following error message: Column 'tblGames.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas why this is occurring? FYI, I know I can remove the function and combine into one call but I'd like to keep the function in place if possible.

Answer 1

I think you should be calling your function like this.

select dbo.fn_GetWinsYear(datepart(yyyy,getdate())) 

OR

select dbo.fn_GetWinsYear('2010') 

Essentially you are just passing a year to your function and the function is returning the number of wins for that year.

If you don't know the year, your function could look something like this...

CREATE FUNCTION dbo.fn_GetWinsYear () 
RETURNS @tblResults TABLE
( W INT, Y INT )

AS 
BEGIN 

    INSERT @tblResults

    SELECT count(1), datepart(yyyy,[Date]) 
    FROM tblGames  
    WHERE WinLossForfeit = 'W'
    GROUP BY datepart(yyyy,[Date]) 


    RETURN
END

SELECT * FROM dbo.fn_GetWinsYear()