0

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?

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
1

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.