Question 113568 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    Your COUNT logic is basically correct except that the ELSE non null value is causing every record to be counted as one. The COUNT function ignores only nulls, so the ELSE value should either be null or ELSE should be omitted entirely:

    SELECT student, COUNT(CASE WHEN score > passing_grade THEN 1 END) AS passed
FROM results
GROUP BY student;

    Note that MySQL supports summing boolean expressions directly, so we can make the above even tighter:

    SELECT student, SUM(score > passing_grade) AS passed
FROM results
GROUP BY student;
    Login or Signup to reply.
  2. 0

    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 🎉), including the query which might help:

    SELECT
    student,
    count(*) as passed
FROM results
WHERE 
    score > passing_grade
GROUP BY
    student
;
    Login or Signup to reply.
  3. 0 
    SELECT C.STUDENT,
SUM(
   CASE
     WHEN C.SCORE>=C.PASSING_GRADE THEN 1
   ELSE 0
   END
 )AS PASSED
FROM RESULTS AS C
GROUP BY C.STUDENT

    The same logic, but with SUM-function

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search