Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have two tables table1 and table2, in table1 I am saving the user details and in table2 the ratings he has given which are multiple rows with same table1.id and ratings in rating column, but when I execute the following code it returns only one row and average of all the ratings instead of the specific user. I am little weak at queries, I guess there needs to be Select within Select , but it is CodeIgniter so am not able to do that. Please help

 $this->db->select('table1.id,table1.name, table1.email, AVG(table2.rating)');
            $this->db->from('table1');
            $this->db->join('table2', 'table1.id = table2.review_id', 'inner');
            $this->db->where(array('table1.status' => 1, 'table1.b_id' => $bid));
            $query = $this->db->get();
            return $query;

What I want is :

> id Name  email            AvG
> 
> 1  name1 [email protected]  average of ratings by this id in table2 
> 2  name2 [email protected]  average of ratings by this id in table2

but what I am getting is 

> id  Name email           AvG
> 
> 1  name1 [email protected]  average of all ratings in table2
share|improve this question

1 Answer

up vote 0 down vote accepted

You need GROUP BY

$this->db->select('table1.id,table1.name, table1.email, AVG(table2.rating)');
$this->db->from('table1');
$this->db->join('table2', 'table1.id = table2.review_id', 'inner');
$this->db->where(array('table1.status' => 1, 'table1.b_id' => $bid));
$this->db->group_by(array('table1.id','table1.name'));
$query = $this->db->get();
return $query;
share|improve this answer
thank you so much, now I am 100% sure I am very bad at SQL.. thank you so much – Deepanshu 16 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.