Aggregate Function alias from select used in where clause

Question

Where is the error here:

SELECT Users.UserID, 
   ClassCourses.CourseID,
   ClassCourses.MinMark,
   sum(StudentMark) as SMark    
FROM   Users 
INNER JOIN UserExams ON Users.UserID = UserExams.UserID
INNER JOIN Exams ON UserExams.ExamID = Exams.ExamID 
INNER JOIN ClassCourses ON Exams.ClassID = ClassCourses.ClassID AND Exams.CourseID = ClassCourses.CourseID
where  ClassCourses.MinMark> SMark
group by Users.UserID, ClassCourses.CourseID,ClassCourses.MinMark

I got an error:

Invalid column name 'SMark'.

Answer 1

You can not use column aliases in your WHERE clause. Also, when comparing results of aggregations, you must use the HAVING clause, after the GROUP BY is applied.

Do something like this:

SELECT Users.UserID, 
    ClassCourses.CourseID,
    ClassCourses.MinMark,
    sum(StudentMark) as SMark    
FROM   Users 
INNER JOIN UserExams ON Users.UserID = UserExams.UserID
INNER JOIN Exams ON UserExams.ExamID = Exams.ExamID 
INNER JOIN ClassCourses ON Exams.ClassID = ClassCourses.ClassID AND Exams.CourseID = ClassCourses.CourseID
GROUP BY Users.UserID, ClassCourses.CourseID,ClassCourses.MinMark
HAVING ClassCourses.MinMark > sum(StudentMark)