Bassem Shahin Bassem Shahin - 3 months ago 14
MySQL Question

error in grouping by year,month in mysql

I have this code, and it is working properly on the online server:

SELECT
DATE(`order`.`date`) AS `dater`,
COUNT(*) AS `orders-amount`,
SUM(`order`.`price`) AS `orders-income`,
(SELECT SUM(`amount`) FROM `paypal` WHERE `paypal`.`txn_id` != 'Bonus' AND YEAR(`dater`) = YEAR(`paypal`.`posted_date`) AND MONTH(`dater`) = MONTH(`paypal`.`posted_date`)) AS `total_charge`
FROM `order`
GROUP BY YEAR(`dater`), MONTH(`dater`)
ORDER BY `dater` DESC


But on localhost it gives an error as below:

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


Id I used this code for grouping then it working properly:

GROUP BY `dater`


This error appear only on localhost as I'm running on linux Mint and installing the apache, php, mysql & pypmyadmin

Answer

You have it in your error message:

this is incompatible with sql_mode=only_full_group_by

If you did not set it on purpose, I guess you got it by default and you have a different version on your local server (see here, it was made default at some point).

So all you need is to disable this mode, either by running SET sql_mode='';, or by following instructions here