chapskev - 1 year ago 57

MySQL Question

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

I have three tables:

- 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"

- 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
```