I am working on a school database and I was trying to count the number of male and female regular students belonging to each academic year.
In my database I have three tables:
Student…….. Containing all student information
Batch……….describing to which academic year a student belongs
Student_history……containing all the terms the student attended class
Since I need only the students who are attending class in the given term I will be using student_history table.
The batch of a student is stored in a string format code for example, DRB0802.
The first three letters describe if the student is regular or not. The next two digits indicate the year the student joined the school and the last two digits tell the term the student joined the school.
I decided to use two queries one for the male students and the other for female students.
Code: Select all
$query_female = "SELECT COUNT(s.student_id) AS femaleNum
FROM batch b ,student s , student_grade_history sgh
WHERE sgh.term_id = $lastTerm
AND sgh.term_year = $reportYear
AND s.student_id = sgh.student_id
AND s.batch_id = b.batch_id
AND s.gender = 'Female'
AND (SUBSTRING(batch_code,1,5)= 'DRB".$selectedYear."' OR SUBSTRING(batch_code,1,5)='DAB".$selectedYear."')
GROUP BY s.gender";
$reportYear is the year the report is being generated.
$lastTerm is the term the student attended class last.
Any suggestions are appreciated.
Thanks a lot.