SQL Server AVG Functions Error

Question

I have two SQL Server tables...

MODULES        ENROLMENTS
GroupNo        StudentNo
Title          GroupNo
Tutor          CourseworkMark
DayNo          ExamMark
Time
Room
Semester

In Access I have created a form based on another table which holds records of course numbers and names. For each course, I wish to create a subform about the modules within that particular course.

In the subform view I wish to have GroupNo, Title, Tutor, DayNo, Time, Room, Semester, Average of CourseworkMark and Average of ExamMark.

I have constructed this query

SELECT tblEnrolments.GroupNo, Tutor, DayNo, Time, Room, Semester, 
       AVG(CourseworkMark) AS AvgCourseworkMark, AVG(ExamMark) AS AvgExamMark
FROM tblEnrolments INNER JOIN tblModules ON tblEnrolments.GroupNo = tblModules.GroupNo
GROUP BY tblEnrolments.GroupNo;

however, I get the error

Msg 8120, Level 16, State 1, Line 1 Column 'tblModules.Tutor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when I run it. How do I get the Tutor, DayNo, Time, Room and Semester fields in the query as they are not part of the GROUP BY or AVG. Thank you

You need to include all the non-aggregated columns in the `group by`. The error message seems pretty clear to me. If that doesn't solve your problem, you should include sample data and desired results. — Gordon Linoff, Mar 19 '15 at 14:44
Add `Tutor, DayNo, Time, Room, Semester` to the group by clause. — jpw, Mar 19 '15 at 14:44
Your title says "SQL Server". Your question says "Access". Which is it? — Becuzz, Mar 19 '15 at 14:45

score 0 · Answer 1 · answered Mar 19 '15 at 14:47

You have to group by all values in your select list , so the query will look like :

SELECT tblEnrolments.GroupNo, Tutor, DayNo, Time, Room, Semester, 
       AVG(CourseworkMark) AS AvgCourseworkMark, AVG(ExamMark) AS AvgExamMark
FROM tblEnrolments INNER JOIN tblModules ON tblEnrolments.GroupNo = tblModules.GroupNo
GROUP BY tblEnrolments.GroupNo, Tutor, DayNo, Time, Room, Semester;

Khurram Ali · Answer 2 · 2015-03-19T14:52:45.837

As this error saying

Msg 8120, Level 16, State 1, Line 1 Column 'tblModules.Tutor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and also mention in the comment @Gordon Linoff:

You need to include all the non-aggregated columns in the group by

SELECT tblEnrolments.GroupNo, 
                     Tutor, 
                     DayNo, 
                     Time, 
                     Room, 
                     Semester, 
                     AVG(CourseworkMark) AS AvgCourseworkMark, 
                     AVG(ExamMark) AS AvgExamMark
FROM tblEnrolments 
INNER JOIN tblModules ON tblEnrolments.GroupNo = tblModules.GroupNo
GROUP BY tblEnrolments.GroupNo ,Tutor, DayNo, Time, Room, Semester ;

SQL Server AVG Functions Error

2 Answers2