CODEWITHSUNDEEP
phpmysqlcodeigniter
user3806999
user3806999

Reputation: 39

Group by COUNT in codeigniter make database error

I have the following code:

public function date_range_customers($params){

       $start_app_date = $params['start_app_date'];
       $end_app_date   = $params['end_app_date'];           


       $this->load->database();
       $this->db->select(
        'COUNT(tbl_appointment.id) AS total',            
        'tbl_customer.cus_name',
        'tbl_customer.cus_email',
        'tbl_customer.cus_mobile'
      );   
      $this->db->join('tbl_customer', 'tbl_customer.id = tbl_appointment.customer_id','inner');
      $this->db->join('tbl_transaction','tbl_transaction.app_id = tbl_appointment.id','inner');
      $this->db->where("app_date BETWEEN '$start_app_date' AND '$end_app_date' AND trans_type_id=1");
      $this->db->group_by('total','desc');
      $query = $this->db->get('tbl_appointment');

When I try to group by 'total' there is error occurred as follows,

A Database Error Occurred Error Number: 1056 Can't group on 'total'

SELECT COUNT(tbl_appointment.id) AS total FROM tbl_appointment INNER JOIN tbl_customer ON tbl_customer.id = tbl_appointment.customer_id INNER JOIN tbl_transaction ON tbl_transaction.app_id = tbl_appointment.id WHERE app_date BETWEEN '2018-01-01' AND '2018-04-30' AND trans_type_id = 1 GROUP BY total

Filename: C:/wamp64/www/theme/system/database/DB_driver.php

Line Number: 691

Upvotes: 1

Views: 123

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You should not group by an aggregated column

but could be you need an order_by for total desc

   $this->load->database();
   $this->db->select(
    'COUNT(tbl_appointment.id) AS total',            
    'tbl_customer.cus_name',
    'tbl_customer.cus_email',
    'tbl_customer.cus_mobile'
  );   
  $this->db->join('tbl_customer', 'tbl_customer.id = tbl_appointment.customer_id','inner');
  $this->db->join('tbl_transaction','tbl_transaction.app_id = tbl_appointment.id','inner');
  $this->db->where("app_date BETWEEN '$start_app_date' AND '$end_app_date' AND trans_type_id=1");
  $this->db->group_by(array('cus_name','cus_email','cus_mobile'));     
  $this->db->order_by('total','desc');
  $query = $this->db->get('tbl_appointment');

and for this you should add in group by the non aggrefated column you have in select

Upvotes: 3

Related Questions