title: sql Calculate boys and girls present and absent
body: <p>Can't manage to come up with a correct query to compute the total no of students </p>
  
  <p>I have three tables:</p>
  
  <ol>
  <li>student - contains student profile either male or female </li>
  </ol>
  
  <p>2.student_attendance - contains attendance details for whether a student was present a either "0" or "1"</p>
  
  <ol start="3">
  <li>attendance - contains all session details where by a one session can be attended by a number of students.</li>
  </ol>
  
  <p>I need to calculate the number of boys/girls in present or absent for a session.</p>
  
  <p>my major headache is to interpreate these logic to sql</p>
  
  <pre><code>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
  </code></pre>
  
  # MY closest sql is its not working :(
  
  <pre><code>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
  </code></pre>