SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

laravel select list is not in group by clause and contains nonaggregated column
sqlstate(42000): syntax error or access violation: 1055 expression laravel
laravel group by not working
this is incompatible with sql_mode=only_full_group_by php
cdbcommand failed to execute the sql statement: sqlstate(42000): syntax error or access violation
eloquent select list is not in group by clause and contains nonaggregated column
in aggregated query without group by
mysql 8.0 only_full_group_by

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

how to solve my problem ?


In your select you have an aggregate function sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause . could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

otherwise if thes are normal columns you should use the normal sintax without ()

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `opportunity`.`logo`, 
        `profile_details`.`user_id`,
        `opportunity`.`name`, 
        `profile_details`.`co_name`,
        `opportunity`.`address`, 
        `opportunity`.`project_type_id`,
        `opportunity`.`state_id` 
FROM `opportunity` 
INNER JOIN `profile_details` ON `opportunity`.`user_id`= `profile_details`.`user_id` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  

How to solve MySQL Syntax error or access violation: 1055 , Expression of SELECT list is not in GROUP BY clause and contains or ORDER BY list refer to nonaggregated columns that are neither named in the How to solve SQLSTATE[42000]: Syntax error or access violation: 1118  Note to OP: An aggregation function in the select clause is like count(), sum(), etc where you're evaluating all values of the grouping. – Ray Nov 17 '16 at 19:08 correct concat is not an aggregation function is linear .. group_concat is an aggregation function – scaisEdge Nov 17 '16 at 19:10


Laravel's groupBy() not working, SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with Posted 3 years ago by tomopongrac (level 50). Reply. SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kamal-oil.ltm


In laravel with MySql go to file config/database.php and it change in array MySql mode strict to false.

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false, //from true
        'engine' => null,
    ],
],

PDOException: SQLSTATE[42000]: Syntax error or access violation , Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column  Run the: sudo mysql -u root -p. And change the SQL Mode for your MySQL Server Instance: mysql > SET GLOBAL sql_mode =(SELECT REPLACE (@ @sql_mode, 'ONLY_FULL_GROUP_BY


Please just copy this line and run it. it worked for me.

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

Youtube video to fix this

Getting this SQL Error: GROUP BY incompatible with sql_mode , The good news is that this has been fixed already in Craft 3 since we Element types can set any custom select and order by values, so those would have to be as well as mcrypt error, as mycrypt is not installed by default in Vagrant box SQLSTATE[42000]: Syntax error or access violation: 1055 Expression and by  I'm an Association member because I think it's important to have a non-profit that has a mission to support Drupal. Ted Bowman, Drupal core maintainer and Senior Software Engineer in OCTO at Acquia


The solution is edit the MySQL config file because the config will revert after every restart...

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add

[mysqld]
sql-mode=""

then restart

sudo systemctl restart mysql

Works on ubuntu 18.04.

Why do I see a PHP Fatal Error saying that the GROUP BY clause is , Why do I see a PHP Fatal Error saying that the GROUP BY clause is incompatible with Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE​[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column  You are not using an aggregation function so could be you are using group by for ordering the result (or for obtain distinct value) starting for mysql 5.6 the use of group by only specifing the all the column involved for group and is not possible a partial specification.


SQLSTATE[42000]: Syntax error or access violation: 1055 , SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains  Thanks for recommending to look into the logs. The clue was the sql_mode. Apparently as of MYSQL 5.7.5, the value is no longer set to empty. There are new default values separated by commas, and the culprit seems to be 'ONLY_FULL_GROUP_BY'.


SQLSTATE[42000]: Syntax error or access violation , SQLSTATE[42000]: Syntax error or access violation: 1055 Expression 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column mn which is not functionally dependent on Add a unique column index to the field and this will be fixed 2. Select Only the field in the groupBy clause. 3. According to the MySQL documentation, having the only full group by mode in the sql mode will reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.


‍ SQLSTATE [42000]: syntax error or access violation: 1055 , SQLSTATE [42000]: syntax error or access violation: 1055 Expression # 3 of the SELECT list is not in the GROUP BY clause and contains non-aggregate ones -  dans vous sélectionnez vous avez une somme de fonction agrégée et un jeu de nom de colonne, l'erreur vous indique que vous n'avez pas spécifié la liste correcte de nom de colonne dans le groupe par clause . peut - être devriez-vous ajouter plus de noms de colonnes dans le groupe par probablement lié au profile_details, opportunity_conditions table