CODEWITHSUNDEEP
phpmysqlsql
chapskev
chapskev

Reputation: 982

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

Upvotes: 1

Views: 336

Answers (1)

sagi
sagi

Reputation: 40481

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

Upvotes: 2

Related Questions