Select specific columns with average of specific row values using joins in CodeIgniter

Question

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

peterm · Accepted Answer · 2013-06-19 08:27:08Z

1

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', 'table1.email'));
$query = $this->db->get();
return $query;

UPDATE To get correct average when rating = 0 you can use the fact that AVG() doesn't take into account NULLs. Therefore you can use IFNULL() or CASE in your select part

$this->db->select('table1.id, table1.name, table1.email, AVG(NULLIF(table2.rating, 0))');

A base sql query should look like

SELECT t1.id, t1.name, t1.email, AVG(NULLIF(t2.rating, 0)) rating
  FROM table1 t1 JOIN table2 t2
    ON t1.id = t2.review_id
 WHERE ...
 GROUP BY t1.id, t1.name, t1.email

edited Jun 19, 2013 at 8:27

answered Jun 12, 2013 at 10:11

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Deepanshu Goyal Over a year ago

thank you so much, now I am 100% sure I am very bad at SQL.. thank you so much

Deepanshu Goyal Over a year ago

peterm, hi can you tell me if there is any way if table2.rating =0, it doesnot get included in average ?

peterm Over a year ago

@Deepanshu You are welcome. And see updated answer for your additional question.

peterm Over a year ago

@Deepanshu Updated the answer. Sorry, typo. Forgot closing parentheses for NULLIF(). Try it again with parentheses.

peterm Over a year ago

@Deepanshu Try execute SQL query directly in myphpadmin with exact where conditions and see if you get correct results.

|

Collectives™ on Stack Overflow

Select specific columns with average of specific row values using joins in CodeIgniter

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related