I have the following tables:

Students(id, name, surname)

Courses(course id)

Course_Signup(id, student_id, course_id, year)

Grades(signup_id, mark)

I want to display all the students(id, name, surname) with their final grade (where final grade = avg of the grades of all courses), but only for the students that have passed all the courses for which they have sign-up in the current year.

This is what I tried:

SELECT s."id", s."name", s."surname", AVG(g."mark") AS "finalGrade"
FROM "STUDENT" s,
    "course sign-up" csn
join "GRADES" g
on csn."id" = g."signup_id"
WHERE csn."year" >= '01-01-2022'
HAVING "finalGrade" >= 5.00
GROUP BY s."id"

However, after adding the last 2 lines, regarding the finalGrade condition, I get an invalid identifier error. Why is that?

CodePudding user response：

Uh, oh. Did you really create tables using lower letter case names enclosed into double quotes? If so, get rid of them (the sooner, the better) because they only cause problems.

Apart from that, uniformly use joins - in your from clause there's the student table which isn't joined to any other table and results in cross join.

Don't compare dates to strings; use date literal (as I did), or to_date function with appropriate format model.

As of error you got: you can't reference expression's alias ("finalGrade") as is in the having clause - use the whole expression.

Also, group by should contain all non-aggregated columns from the select column list.

This "fixes" error you got, but - I suggest you consider everything I said:

SELECT s."id", s."name", s."surname", AVG(g."mark") AS "finalGrade"
FROM "STUDENT" s,
    "course sign-up" csn
join "GRADES" g
on csn."id" = g."signup_id"
WHERE csn."year" >= date '2022-01-01'
GROUP BY s."id", s."name", s."surname"
HAVING AVG(g."mark") >= 5.00