sql_mode=only_full_group_by is incompatible in PHP mysql query with GROUP BY codeigniter

#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customers.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Add $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));") line above your query.

See Example :

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
$this->db->select('*, customers.first_name, customers.last_name, customers.id');
$this->db->select("(SELECT count(video_id) FROM customer_videos WHERE c.customer_id = customer_videos.customer_id AND (created_at >= '$from' OR created_at <= '$todate')) as seen");
$this->db->join('customers', 'customers.id = c.customer_id', 'left');
$this->db->group_by('c.customer_id');
$this->db->from('customer_videos as c');
$query = $this->db->get();
echo "<pre/>"; print_r($query->result());exit;
return $query->result_array();

SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by

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

will be simply solved by changing the sql mode in MySQL by this command,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

OR

set @@global.show_compatibility_56=ON;

OR from CMD

mysql -u root -p mg_cstore < db.sql

This is too works me.. I used this becz in my project there are many Queries like this so just change this sql mode only_full_group_by


For permenant solution in ubuntu 16 and 18

Step1) find and modify the config file my.cnf. Usually it’s in /etc/my.cnf or /etc/mysql/my.cnf.

Step 2) Add following code end of the file
[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

How to get the latest record in each group using GROUP BY

In PHP MySQL

SELECT `main_table`.`rebate_amount`, `main_table`.`companyname`, (SELECT target FROM batecash WHERE id = MAX(main_table.id)) AS `maxtarget`, SUM(rebate_amount) AS `rebate_amount`, SUM(invoiceamount) AS `purchase_amount`, MAX(id) AS `max_id`, `main_table`.`rebatetarget`, YEAR(created_at) yr, QUARTER(created_at) qt FROM `batecash` AS `main_table` WHERE (customerid = '5') GROUP BY YEAR(created_at), QUARTER(created_at) ORDER BY `created_at` DESC

 In Magento

$rebatecount = Mage::getModel('rebatereward/batecash')->getCollection()
                    ->addFieldToSelect('rebate_amount')
                    ->addFieldToSelect('companyname')
                    ->addFieldToFilter('orderstatus', array('eq' => 'completed'))
                    ->addFieldToFilter('addressid', array('eq' => $address->getId()))
                    ->addFieldToFilter('customerid', array('eq' => $this->getCustomer()->getId()));

            $rebatecount->getSelect()->columns("(SELECT rebatetarget FROM batecash WHERE id = MAX(main_table.id)) as maxtarget");
            $rebatecount->getSelect()
                    ->columns('SUM(rebate_amount) as rebate_amount')
                    ->columns('SUM(invoiceamount) as purchase_amount')
                    ->columns('MAX(id) as max_id')
                    ->columns('rebatetarget')
                    ->columns('YEAR(created_at) yr, QUARTER(created_at) qt')
                    ->group('YEAR(created_at), QUARTER(created_at)')
                    ->order(array('created_at DESC'));

Codeigniter Query in mysql With JOIN, GroupBy, Select Into Select, IFNULL, SUM, IF Condition

Codeigniter Query

function getitemsofseller($sellers){
       
        //For get total funds of the seller and set flag on item
        $i=0;
        $banne = 0;
        $results = array();
        foreach ($sellers as $seller):
           
            $this->db->select('*');
       
            /*Get homepage banner fee*/
            $this->db->select("SUM(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) )) as mainbannerfee");
           
            /*Get feature banner fee*/
            $this->db->select("SUM(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) )) as featurefee");
           
            /*Get nominal fee*/
            $this->db->select("SUM(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2))) as nominalfee", false);
           
            /*Get Total payment which is payed to seller ((paid_price+shipping_price)*quantity)-mainbannerfee-featurefee-nominalfee*/
            $this->db->select("(SUM((((paid_price+shipping_price)*quantity)"
                    . "-(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2)))))) as sellerpaid", false);
           
            /*Get return product payment (SUM(((paid_price+shipping_price)*quantity)+home_page_banner+featurebanner+nominalfee) */
            $this->db->select("IFNULL((SELECT SUM(((paid_price+shipping_price)*quantity)+(IF(home_page_banner = 1, (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))+(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ), ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))) FROM (".prefix('order_items')." as btoi) WHERE `seller_id` = oi.seller_id AND btoi.lisiting_id = oi.lisiting_id AND `dispute_raised` = '0' AND `ispaidtoseller` = '0' AND `is_paid` = '3' AND `item_status` = 'Completed' GROUP BY `lisiting_id`),0) as refunds", false);
           
            /*Get return product payment (((paid_price+shipping_price)*quantity)-mainbannerfee-featurefee-nominalfee)-returned ((SUM(((paid_price+shipping_price)*quantity)+home_page_banner+featurebanner+nominalfee)) */
            $this->db->select("(SUM((((paid_price+shipping_price)*quantity)"
                    . "-(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2)))))"
                    . "-IFNULL((SELECT SUM(((paid_price+shipping_price)*quantity)+(IF(home_page_banner = 1, (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))+(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ), ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))) FROM (".prefix('order_items')." as btjoi) WHERE `seller_id` = oi.seller_id AND btjoi.lisiting_id = oi.lisiting_id AND `dispute_raised` = '0' AND `ispaidtoseller` = '0' AND `is_paid` = '3' AND `item_status` = 'Completed' GROUP BY `lisiting_id`),0)) as sellerpaidafterfee", false);
           
           
            /*Get Return Qty (if is_paid = 3)*/
            $this->db->select("IFNULL((SELECT SUM(quantity) FROM ".prefix('order_items').' WHERE dispute_raised = 0 && item_status = "Completed" && ispaidtoseller = 0 && is_paid = 3 && `lisiting_id` = oi.lisiting_id LIMIT 1),0) as returnqty', false);
           
            /*Gross Revenue SUM((paid_price+shipping_price)*quantity)*/
            $this->db->select("SUM((paid_price+shipping_price)*quantity) as grossrevenue", false);
           
            /*Get nominal fee in percentage(%) */
            $this->db->select("IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0) as nominalfeeper", false);
           
            /* Get total shipping charge */
            $this->db->select("SUM(shipping_price) as totalshipping", false);
           
            /* Get total product price without fee*/
            $this->db->select("SUM(paid_price)*quantity as totalproductprice", false);
           
            /* Get total quantity without calculate returned*/
            $this->db->select("SUM(quantity) as unitsold");
           
            /* Get total quantity without calculate returned*/
            $this->db->select(MAIN_BANNER_FEE." as mainbannerfeestatic", false);
           
            $this->db->select("IF(is_buytribe = 1 , ".FEATURE_BT_FEE." , ((1*".FEATURE_BIN_FEE.")) ) as featurefeestatic", false);
           
            $this->db->from("order_items as oi");
            $this->db->group_by('lisiting_id');
            $this->db->where('seller_id', $seller->seller_id);
            $this->db->where('dispute_raised', '0');
            $this->db->where('ispaidtoseller', '0');
            $this->db->where('item_status', 'Completed');
           
            $query = $this->db->get();
           
            //echo $this->db->last_query();exit;
            if($query->num_rows() > 0){
                $results[$i] = $query->result();
            }
            $i++;
        endforeach;
       
        return $results;
    }