count specific values and display each separately

Question

I have the following tables projects(project_id,project_name) tasks(tid,project_id,status)

I want to count each status (in progress,completed,pending) and display in separate columns

My attempt:

controller:

public function overview(){
        $data['main_view'] = 'projects/overview';
       
        $data['data']=$this->project_model->get_data();

 
          $this->load->view('layouts/main' , $data);
}

view:

<table class="table table-bordered">
        
            <thead>
            <tr> 
                <th>#</th>
                <th>Project Name</th>
                <th>Client Name</th>
                <th>Due</th>
                <th>Pending Tasks</th>
                <th>In progress Tasks</th>
                <th>Completed</th>
                
            </tr>
            </thead>
            <tbody>
                <?php foreach ($data as $row): ?>
                <tr>
                    <td><?php echo $row->project_id ?></td>
                    <td><?php echo $row->project_name ?></td>
                    <td><?php echo $row->name ?></td>
                    <td><?php echo $row->due_date ?></td>
                    <td><?php echo $row->pending_tasks ?></td>
                    <td><?php echo $row->in_progress ?></td>
                    <td><?php echo $row->completed_tasks ?></td>
                  
                    
                </tr>
                <?php endforeach;?>
            </tbody>
       
        
    </table>

Model:

public function get_data(){
        $status = array(
            'status' =>'Completed' ,
            'status' =>'Pending',
            'status' =>'In progress'
        );
        $this->db->select("projects.*,Count('tasks.*') as pending_tasks,Count('tasks.*') as completed_tasks,Count('tasks.*') as in_progress,clients.name");
        $this->db->join('tasks','tasks.project_id=projects.project_id');
        $this->db->join('clients','clients.id=projects.client_id');
        $this->db->where_IN(['tasks.status'=> $status]);
        $this->db->group_by('tasks.project_id');
        $q =$this->db->get('projects');
        return $q->result();
        
}

The result i get is the total number of tasks with the corresponding statuses in each column . its not split up

score 0 · Answer 1 · answered Jul 13 '20 at 12:34

0

Use SUM IF (or CASE WHEN) in your select:

$this->db->select("projects.*,COUNT(SUM(IF(tasks.status='Pending',1,0))) as pending_tasks,COUNT(SUM(IF(tasks.status='Completed',1,0))) as completed_tasks,COUNT(SUM(IF(tasks.status='In progress',1,0))) as in_progress,clients.name");

This counts only the rows that match the condition. See https://www.w3schools.com/sql/func_mysql_if.asp.

answered Jul 13 '20 at 12:34

Tum

6,937
2
25
23

Im getting an error: A Database Error Occurred Error Number: 1111 Invalid use of group function – user13922161 Jul 14 '20 at 06:09
Please help me out ,Im new to this – user13922161 Jul 14 '20 at 06:18
I think you need to add 'tasks.project_id' to your select statement, i.e.: `$this->db->select("projects.*,tasks.project_id,COUNT(...`. Or you can disable FULL_GROUP_BY (https://stackoverflow.com/questions/23921117/disable-only-full-group-by). – Tum Jul 15 '20 at 08:58

count specific values and display each separately

1 Answers1