Count number of values in a single row

[hari.sarvothama]

Hello,
My db table is this (semester result table)

sem1
regno(int) s1(enum) s2(enum), s3(enum), s4(enum)

I want to find number of subjects in which the student with given id has gotten a particular grade. (ie no of subjects he/she has failed).
Is it possible or shld i change the db design itself?

[smantscheff]

You should at least alter the column names. How do you anyone expect to debug this database with names like "s1" or "regno"?

For normalization purposes the subjects should be in their own table and there should be a relation students_and_subjects.

If regno is the student's id and s1 to s4 the grades in up to 4 subjects then you can query the subject count above a certain GRADE with this query:

select regno, if(s1 >= @grade, 1, 0) + if(s2 >= @grade, 1, 0) + if(s3 >= @grade, 1, 0) + if(s4 >= @grade, 1, 0) as number_of_passed_subjects
from sem1
where regno=@student_id