CODEWITHSUNDEEP
phpcodeigniterjoingroup-bysum
Mike
Mike

Reputation: 11

codeigniter need help grouping and sum of tables

Here is my situation. 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 there 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

== UPDATE ==

Thank you user3774708. That little bit that you provided I was able to solve the rest. Here is the rest of the code that I needed to change: I changed: 

activity_hours.hours

to 

sum(activity_hours.hours) as hours

final database query

$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();

Upvotes: 0

Views: 644

Answers (1)

Abhineet Kashyap
Abhineet Kashyap

Reputation: 180

Use Group_by on users.id

$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');
$this->db->group_by('users.id');
$query = $this->db->get();
return $query->result();

Upvotes: 2

Related Questions