chapskev chapskev - 6 months ago 15
MySQL Question

sql Calculate boys and girls present and absent

Can't manage to come up with a correct query to compute the total no of students

I have three tables:


  1. student - contains student profile either male or female



2.student_attendance - contains attendance details for whether a student was present a either "0" or "1"


  1. attendance - contains all session details where by a one session can be attended by a number of students.



I need to calculate the number of boys/girls in present or absent for a session.

my major headache is to interpreate these logic to sql

if(in_attendace =1) then
sum the number of boys as boys_present
sum the number of girls as girls_present
else
sum the number of boys as boys_absent
sum the number of girls as girls_absent


# MY closest sql is its not working :(

select
case when a.in_attendance = 1 then
SUM(CASE b.gender when 1 then 1 else 0 end ) as male_present,
SUM(CASE b.gender when 2 then 1 else 0 end ) as female_present,
ELSE
SUM(CASE b.gender when 1 then 1 else 0 end ) as male_absent,
SUM(CASE b.gender when 2 then 1 else 0 end ) as female_absent
END
from attendance_student as a inner join student as b on a.student_id = b.id where a.session_details_id = 38

Answer

Well, you are not very far from the solution, you just need to separate them into different columns(I assume that's what you want) :

select COUNT(CASE WHEN a.in_attendance = 1 and b.gender = 1 then 1 END) as male_present,
       COUNT(CASE WHEN a.in_attendance = 1 and b.gender = 2 then 1 END) as female_present,
       COUNT(CASE WHEN a.in_attendance = 0 and b.gender = 1 then 1 END) as male_absent,
       COUNT(CASE WHEN a.in_attendance = 0 and b.gender = 2 then 1 END) as female_absent
FROM attendance_student a
INNER JOIN student b
 ON a.student_id = b.id 
WHERE a.session_details_id = 38
Comments