In Codeigniter : How to Count in table using Join in mysql [duplicate]

Question

I have two tables company,rate

Company has two columns-

v_id
company_name

user_ratings has three columns

r_id
v_id
rate

In this rate v_id column,I have company_id (v_id) which has two different rating and when i count the ratings for one company every time i am getting only one. How to count the ratings for company.

function get_list() {

        $this->db->select('company.company_name, COUNT(user_ratings.rate) as rate');
        $this->db->from('company_details');
        $this->db->join('user_ratings', 'company.v_id = user_ratings.v_id');
        $this->db->group_by('company.company_name, rate');
        $query = $this->db->get();

        return  $query->result();
    }

Its return only one record from user rating table because of goup_by..try after removing group by — Gopal Bhuva
– Gopal Bhuva, Commented Apr 17, 2017 at 12:47
after removing group by i am getting error like In aggregated query without GROUP BY — Vishal Kumar
– Vishal Kumar, Commented Apr 17, 2017 at 12:57

Samir Selia · Accepted Answer · 2017-04-17 12:48:50Z

1

You have grouped results with both company_name and rate. This is why it's returning count as 1 for all records. Grouping only by company name will return desired results.

Change this

$this->db->group_by('company.company_name, rate');

to

$this->db->group_by('company.company_name');

Also, company is used as an alias for company_details table but its not created.

$this->db->from('company_details AS company');

edited Apr 17, 2017 at 12:48

answered Apr 17, 2017 at 12:42

Samir Selia

7,1502 gold badges14 silver badges33 bronze badges

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

5 Comments

Samir Selia Over a year ago

It's giving proper data. sqlfiddle.com/#!9/757778/2. For company ID 3 there is only 1 rating, so rate value is 1. For company ID 1 there are 2 ratings, so rate value is 2.

Vishal Kumar Over a year ago

i am little bit shocked, Because this query is running on sqlfiddle But on mysql this query is throwing error.

Vishal Kumar Over a year ago

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'search.company.v_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Gopal Bhuva Over a year ago

Remove sql mode (FULL_GROUP_BY) from your sql config file

Vishal Kumar Over a year ago

Thank You Guys.. i Don't need to remove (FULL_GROUP_BY) .

Collectives™ on Stack Overflow

In Codeigniter : How to Count in table using Join in mysql [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related