CODEWITHSUNDEEP
mysqlcodeigniter
Ronald Torres
Ronald Torres

Reputation: 199

Adding multiple count in a group by query using codeigniter

My Table looks like this:

| id | employee_id |   status  |
   1      2           Present
   2      2           Present
   3      2           Absent
   4      2           Holiday

here is my query:

    $this->db->select('employee_id,COUNT(*) as num_present',FALSE);
    $this->db->group_by('employee_id'); 
    $this->db->group_by('status'); 
    $query  =  $this->db->get('xin_payroll_temp'); 
    $data = $query->result();

    print json_encode($data);

Output of the query:

  {
    "employee_id": "2",
    "num_present": "2"
  },
  {
    "employee_id": "2",
    "num_present": "1"
  },
  {
    "employee_id": "2",
    "num_present": "1"
  }

My query counts the Present , Absent , Holiday, but the problem is that it displays it in separate array data.

What i want is to display it in a single array with this format:

{
 "employee_id": "2",
 "num_present": "2",
 "num_absent" : "1",
 "num_holiday" : "1"
}

Any help would be appreciated. Thanks.

Upvotes: 0

Views: 775

Answers (1)

Ronald Torres
Ronald Torres

Reputation: 199

After a long search on google i finally solved the problem. Here is the query i used to achieve the format that i want:

        $query = $this->db->select('employee_id, sum(case when status = "Present" then 1 else 0 end ) As present_count, sum(case when status = "Absent" then 1 else 0 end ) As absent_count, sum(case when status = "Holiday" then 1 else 0 end ) As holiday_count',FALSE)
          ->from("xin_payroll_temp")
           ->group_by('employee_id')
           ->get();

    $data = $query->result();

    print json_encode($data);

Upvotes: 1

Related Questions