SQL - for each id, count how many entries in one column is greater than another-CodePudding

Suppose I have a table 'results' that looks like this:

student | score | passing_grade
1 | 50 | 70
1 | 30 | 50
2 | 90 | 50
2 | 80 | 100

I want to count, for each student, how many tests they've passed. The result should be as followed:

student | passed
1 | 0
2 | 1

explanation: student 1 has passed none of the tests, while student 2 has passed 1 out of 2 tests based on the conditions of the second and third column.

I don't know if it makes a difference but I created results by merging 2 tables together. I've tried to use the WHERE clause to find all rows where the score > passing_grade but I don't know if that is in the right direction. I've also tried the COUNT(CASE WHEN score > passing_grade THEN 1 ELSE 0 END) but I'm not sure if I'm doing it right since this counts every row.

CodePudding user response：

It's a simple SQL query. I would recommend reading SQL basics and trying a bit before posting a question. But since you're a new contributor here (welcome aboard