1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql mode only full group by

Question

my Query    select libelle credit initial disponible v sum montant  as montant  FROM fiche annee type where type id type annee id type and annee id annee fiche id annee  and annee   year current timestamp  GROUP BY libelle order by libelle asc    Mysql 5 7 9 runs the query without any problem but mysql 5 7 12 display the error above Could you please help me with that

User · Answer

You can disable sql mode only full group by by some command you can try this by terminal or MySql  IDE  mysql gt  set global sql mode  STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION    mysql gt  set session sql mode  STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION     I have tried this and worked for me  Thanks

User · Answer

I had a struggle getting this to work i ve tested it and it s working on lamp server mysql version 5 12  So  steps to success     sudo vim  etc mysql conf d mysql cnf Scroll to the bottom of file Copy and paste    mysqld  sql mode STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   to the bottom of the file   save and exit input mode sudo service mysql restart to restart MySQL    Done

User · Answer

You need to specify all of the columns that you re not using for an aggregation function in your GROUP BY clause like this   select libelle credit initial disponible v sum montant  as montant  FROM fiche annee type where type id type annee id type and annee id annee fiche id annee  and annee   year current timestamp  GROUP BY libelle credit initial disponible v order by libelle asc   The full group by mode basically makes you write more idiomatic SQL  You can turn off this setting if you d like  There are different ways to do this that are outlined in the MySQL Documentation  Here s MySQL s definition of what I said above      MySQL 5 7 5 and up implements detection of functional dependence  If   the ONLY FULL GROUP BY SQL mode is enabled  which it is by default     MySQL rejects 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 them   Before   5 7 5  MySQL does not detect functional dependency and ONLY FULL GROUP BY is not enabled by default  For a description of   pre-5 7 5 behavior  see the MySQL 5 6 Reference Manual     You re getting the error because you re on a version  lt  5 7 5

User · Answer

So let s fully understand  Let s say you have a query which works in localhost but does not in production mode  This is because in MySQL 5 7 and above they decided to activate the sql mode only full group by by default  basically it is a strict mode which prevents you to select non aggregated fields    Here s the query  works in local but not in production mode      SELECT post    YEAR created at  as year FROM post GROUP BY year  SELECT post id  YEAR created at  as year     This will generate an error since there are many ids FROM post GROUP BY year   To verify if the sql mode only full group by is activated for  you should execute the following query     SELECT   sql mode      localhost   Output   IGNORE SPACE  STRICT TRANS  ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION  If you don t see it  it means it is deactivated   But if try in production mode  or somewhere where it gives you the error it should be activated    SELECT   sql mode      production   Output  ONLY FULL GROUP BY  STRICT TRANS TABLES  NO ZERO IN DATE  NO ZERO    And it s ONLY FULL GROUP BY we re looking for here   Otherwise  if you are using phpMyAdmin then go to -  Variables and search for sql mode  Let s take our previous example and adapt it to it     SELECT MIN post id   YEAR created at  as year    Here we are solving the problem with MIN   FROM post GROUP BY year   And the same for MAX    And if we want all the IDs  we re going to need   SELECT GROUP CONCAT post id SEPARATOR       YEAR created at  as year FROM post GROUP BY year   or another newly added function   SELECT ANY VALUE post id   YEAR created at  as year  FROM post GROUP BY year      ANY VALUE does not exist for MariaDB  And If you want all the fields  then you could use the same   SELECT ANY VALUE post id   ANY VALUE post slug   ANY VALUE post content  YEAR created at  as year  FROM post GROUP BY year     To deactivate the sql mode only full group by then you ll need to execute this query    SET GLOBAL sql mode  SELECT REPLACE   sql mode   ONLY FULL GROUP BY           Sorry for the novel  hope it helps

User · Answer

Solution 1  Remove ONLY FULL GROUP BY from mysql console  mysql  gt  SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY          you can read more here  Solution 2  Remove ONLY FULL GROUP BY from phpmyadmin  Open phpmyadmin  amp  select localhost Click on menu Variables  amp  scroll down for sql mode Click on edit button to change the values  amp  remove ONLY FULL GROUP BY  amp  click on save

User · Answer

Base ond defualt config of 5 7 5 ONLY FULL GROUP BY  You  should use all the not aggregate column in your group by   select libelle credit initial disponible v sum montant  as montant  FROM fiche annee type  where type id type annee id type  and annee id annee fiche id annee  and annee   year current timestamp   GROUP BY libelle credit initial disponible v order by libelle asc

User · Answer

You have to aggregate by anything NOT IN the group by clause   So there are two options   Add Credit Initial and Disponible v to the group by  OR  Change them to MAX  Credit Initial   as Credit Initial  MAX  Disponible v   as Disponible v if you know the values are constant anyhow and have no other impact

User · Answer

This worked for me   mysql -u root -p mysql  gt  SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY          You might need sudo for the first step   sudo mysql -u root -p

User · Answer

Same thing is happened with 8 0  versions as well  By default in 8 0  version it is  enabled  by default  Here is the link official document reference  In case of 5 6   5 7  versions  the property  ONLY FULL GROUP BY  is disabled by default   To disabled it  follow the same steps suggested by  Miloud BAKTETE

User · Answer

just go to the window bottom tray click on wamp icon  click mysql- my ini then there is option  sql-mode    uncomment this make it like sql-mode    and restart wamp worked for me

[mysql] #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

Examples related to mysql

Examples related to mysql-error-1055