A table grade looks like this

It shows the grade of a student for all courses in each of the calendar year. A course can be taken multiple times every year. The datatype of pass is bool. Now I want to know all the courses which get passed at least one time in each of the year 2020 and 2021.

My query is

SELECT course FROM grade
WHERE year IN (2020, 2021)
GROUP BY course, year
HAVING SUM(pass) > 0;

But it shows the courses satisfy either year 2020 or 2021. How to only show the courses satisfy both year 2020 and 2021?

CodePudding user response：

This query could work

SELECT course 
FROM grade
WHERE year IN (2020, 2021)
AND pass = 'yes'
GROUP BY course
HAVING COUNT(DISTINCT year) = 2

CodePudding user response：

I'm not sure if all the corner cases will be covered with this query, but you could try it out;

SELECT course FROM grade WHERE pass='yes' and (year=2021 or year=2020);