php - 在 codeigniter 中按 COUNT 分组使数据库错误

Question

我有以下代码:

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');

当我尝试按“总计”分组时，出现如下错误，

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

Answer 1

你不应该按聚合列分组

但是你可能需要一个 order_by 来获取总描述

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

为此，您应该按选择中的非聚合列添加到组中