Calculate total males and females in each group with CodeIgniter

user_17598975 0
Viewed 214

How can I find the number of males and females in each section using CodeIgniter's query builder methods?

I've already tried Count, select, sum with grouping, but I still cannot achieve my desired result.

What I want is to get the section name, its total students, and its separate male and female counts. I want do this with 1 query.

Desired output:

Male Female
Section 1 10
Section 2 8

Table Schema:

  • tbl_section: section_id, section_name
  • tbl_student: stud_id,stud_name,gender,section_id(foreign key)

My coding attempt:

$total =$this->db
    ->selectCount('gender')
    ->from(tbl_student)
    ->join('tbl_section','tbl_student.section_id = tbl_section.section_id)
    ->where('tbl_student.gender = "male")
    ->get()
    ->result_array();

Raw SQL attempt:

select section.section_name,count(stud_id)
from tbl_student
join section on tbl_student.section_id=section.section_id
where tbl_student.gender = "Male"
group by section.section_id;

Outcome: Only 1 gender displayed -- I want to display both male and female.

aggregate-functions codeigniter group-by mysql php
1 Answers

You want to group by the section ids, then calculate two conditional counts in the select that relate to each grouped section id.

The raw SQL looks like this: (db-fiddle demo)

SELECT sec.section_name, SUM(gender = 'M') males, SUM(gender = 'F') females
FROM tbl_section AS sec
JOIN tbl_student AS stu ON sec.section_id = stu.section_id
GROUP BY section_name

To translate this to CI's query builder methods:

return $this->db
    ->select("sec.section_name section, SUM(gender = 'M') males, SUM(gender = 'F') females")
    ->from('tbl_section sec')
    ->join('tbl_student stu', 'sec.section_id = stu.section_id')
    ->group_by('section_name')
    ->get()
    ->result();
user_29434030
Related