Member Avatar for 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?

  • 2 Contributors
  • 1 Reply
  • 97 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for 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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.