CODEWITHSUNDEEP
mysqlsql
UnhappyGhost
UnhappyGhost

Reputation: 13

Count specific rows to match the query

I have a table named results retrieved and displayed as follows

Working table


the columns sub1 to sub2 represents the subject names and the rows have values scored by the students

While the information is retrieved from the db, I also need to count in how many subjects did a student score less than 40 for example, Tom scored less than 40 in 2 subjects and the result would look like as follows

Working table


Please help how to write a query to display the last column

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

MySQL has the convenient ability to treat boolean comparisons as integers. Here is a pretty simple way to express your logic:

select id, students, sub1, sub2, sub3, sub4, sub5, sub6,
       ((sub1 < 40) + (sub2 < 40) + (sub3 < 40) + (sub4 < 40) + (sub5 < 40) + (sub6 < 40)
       ) as FailedIn 
from table t;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

Normalizing your table would have made this much easier, but even without changing its structure, you can get this result with some case statements:

SELECT id, students, 
       sub1, sub2, sub3, sub4, sub5, sub6,
       CASE WHEN sub1 < 40 THEN 1 ELSE 0 END +
       CASE WHEN sub2 < 40 THEN 1 ELSE 0 END +
       CASE WHEN sub3 < 40 THEN 1 ELSE 0 END +
       CASE WHEN sub4 < 40 THEN 1 ELSE 0 END +
       CASE WHEN sub5 < 40 THEN 1 ELSE 0 END +
       CASE WHEN sub6 < 40 THEN 1 ELSE 0 END AS "Failed IN"
FROM   my_table

Upvotes: 1

Related Questions