phpcodeigniterjoingroup-bysum

How to group results and sum one of the columns?


I have 2 tables. I am trying to combine the 2 tables, which I have been successful with join. Now I need to add up the values, but I don't want to repeat the same user when I display.

Example tables:

Table 1:

users
[ id ][name    ]
[  1 ][John Doe]
[  2 ][Jane Doe]
[  3 ][Joe  Doe]

Table 2:

activity_hours
[ id ][user_id][hours]
[  1 ][  1    ][  3  ]
[  1 ][  2    ][  1  ]
[  1 ][  3    ][  4  ]
[  1 ][  1    ][  2  ]
[  1 ][  2    ][  3  ]

I want to add up all the hours for each user from activity hours, but want to match the amount to their name

Here is my current code: Hours_model.php

    $this->db->select('
        users.id as id,
        users.name,
        activity_hours.hours
    ');

    $this->db->from('users');
    $this->db->join('activity_hours', 'users.id = activity_hours.user_id');

    $query = $this->db->get();
    return $query->result();

hours_veiw.php

foreach ($result as $activity) {
  echo $activity->name.' : '.$activity->hours;
}

Desired output:

John Doe : 5
Jane Doe : 4
Joe Doe  : 4

Solution

  • Use Group_by on users.id

    $this->db->select('
        users.id as id,
        users.name,
        sum(activity_hours.hours) as hours
    ');
    
    $this->db->from('users');
    $this->db->join('activity_hours', 'users.id = activity_hours.user_id');
    $this->db->group_by('users.id');
    $query = $this->db->get();
    return $query->result();