Column 'dbo.Class2.ID' is invalid in the select list because it is not contained in either an aggregate function or the group by claus

Question

I am trying to create a Sql function,Where a list of students is fetched by specifying their class status.I could create a function to retrieve such an information for a single user,But when i tried to get a list for all of them i get this error. This is my Functions declaration in Sql:

    CREATE FUNCTION dbo.Function_GetListOfStudentsWithClassStatus
    (
@ClassTypeid int,
@VerificationCode bit
    )
RETURNS TABLE/* @table_variable TABLE (column1 datatype, column2 datatype) */
AS

    RETURN SELECT        *, COUNT(*) AS CLassNumbers
FROM            dbo.tblClass2
WHERE         (ClassTypeID = @ClassTypeid) AND (Verified = @VerificationCode)
GROUP BY StNID

score 1 · Accepted Answer · answered Aug 12 '12 at 10:19

When using Aggregate Functions (f.e. you are using COUNT), you should always include all of the fields that are being selected, but are not being processed by said functions, in the GROUP BY clause of the query.

In your example, you are selecting "*", which means that every field in that table needs to be mentioned in your GROUP BY clause.

Column 'dbo.Class2.ID' is invalid in the select list because it is not contained in either an aggregate function or the group by claus

1 Answers1