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

Question

AM using MySQL 5 7 13 on my windows PC with WAMP Server  Here my Problem is While executing this query  SELECT   FROM  tbl customer pod uploads  WHERE  load id     78  AND        status     Active  GROUP BY  proof type    Am getting always error like this      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   Can you please tell me the best solution     I need Result like   ---- --------- --------- --------- ---------- ----------- ------------ --------------- -------------- ------------ -------- --------------------- ---------------------    id   user id   load id   bill id   latitude   langitude   proof type   document type   file name      is private   status   createdon             updatedon              ---- --------- --------- --------- ---------- ----------- ------------ --------------- -------------- ------------ -------- --------------------- ---------------------     1         1   78        1         21 1212    21 5454              1               1   id Card docx            0   Active   2017-01-27 11 30 11   2017-01-27 11 30 14    ---- --------- --------- --------- ---------- ----------- ------------ --------------- -------------- ------------ -------- --------------------- ---------------------

User · Accepted Answer

This      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          This too works for me   I used this  because in my project there are many Queries like this so I just changed this sql mode to only full group by  Thank You     -

User · Answer

When MySQL s only full group by mode is turned on  it means that strict ANSI SQL rules will apply when using GROUP BY   With regard to your query  this means that if you GROUP BY of the proof type column  then you can only select two things    the proof type column  or aggregates of any other column    By  aggregates  of other columns  I mean using an aggregate function such as MIN    MAX    or AVG   with another column   So in your case the following query would be valid   SELECT proof type         MAX id  AS max id         MAX some col          MIN some other col  FROM tbl customer pod uploads WHERE load id    78  AND       status    Active  GROUP BY proof type   The vast majority of MySQL GROUP BY questions which I see on SO have strict mode turned off  so the query is running  but with incorrect results   In your case  the query won t run at all  forcing you to think about what you really want to do   Note  The ANSI SQL extends what is allowed to be selected in GROUP BY by also including columns which are functionally dependent on the column s  being selected   An example of functional dependency would be grouping by a primary key column in a table   Since the primary key is guaranteed to be unique for every record  therefore the value of any other column would also be determined   MySQL is one of the databases which allows for this  SQL Server and Oracle do not AFAIK

User · Answer

gt  sudo nano  etc mysql my cnf   Enter below   mysqld  sql mode        Ctrl   O    Y   Ctrl   X   gt  sudo service mysql restart

User · Answer

In your my ini  write this    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    depend on your version  Or    mysqld  sql mode        or simply remove this  ONLY FULL GROUP BY

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

User · Answer

Use any one solution  out of below 3   1  PHPMyAdmin if you are using phpMyAdmin then change the  quot sql mode quot  setting as mentioned in the below screenshot   Edit  quot sql mode quot  variable and remove the  quot ONLY FULL GROUP BY quot  text from the value OR  2  SQL Command prompt Run the below command  SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY         OR  3  Don t use SELECT   Use relevant column in SELECT query  relevant means columns  which are either coming in  quot group by quot  clause or column with the aggregate function  MAX  MIN  SUM  COUNT etc   Important note Changes made by using point 1  OR point 2  does not set it PERMANENTLY  and it will revert after every restart  So you should set this in your config file  e g   etc mysql my cnf in the  mysqld  section   so that the changes remain in effect after MySQL restart  Config File   etc mysql my cnf Variable name   sql mode OR sql-mode Remove word ONLY FULL GROUP BY from the value and save the file  Note   If you have not found  quot sql mode quot  variable in the config file than please insert below 2 lines at the end of the file  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

User · Answer

Hi instead of taking all columns  just take what you need by using ANY VALUE column name   It is working perfectly  Just check  E g   SELECT proof type any value  quot customer name quot   as customer name FROM  tbl customer pod uploads  WHERE  load id     78  AND  status     Active  GROUP BY  proof type

User · Answer

Here is a really fast and easy way of setting it permanently  NB  running SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY         is temporary and on server restart you will still end up with the error   To fix this permanently do the below    Login to your server as root in your terminal run  wget https   gist githubusercontent com nfourtythree 90fb8ef5eeafdf478f522720314c60bd raw disable-strict-mode sh Make the script executable by running chmod  x disable-strict-mode sh Run the script by running   disable-strict-mode sh   And your done   changes will be made to mysql and it will be restarted

User · Answer

From how it looks  I think grouping by multiple columns fields wont hurt your result  Why don t you try adding to the group by like this   GROUP BY  proof type    id    This will group by proof type first then id  I hope this does not alter the results  In some most cases group by multiple columns gives wrong results

User · Answer

In Ubuntu   Step 1    sudo vi  etc mysql mysql conf d mysqld cnf   Step 2  Go to last line and add the following  sql mode        Step 3  Save  Step 4  Restart mysql server

User · Answer

I had similar problem with part of my database crushing  What I did is I changed the parameter in the DB via PHPStorm database console like this     SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY         works like charm now

User · Answer

only full group by   on tells MySQL engine  Do not apply GROUP BY when you have doubt about what results to show and throw an error  only apply it if the command specifically tells you what to do  i e  when the command is full and complete  only full group by   off tells MySQL engine  always apply GROUP BY and if you have doubt about what results to choose  just pick one randomly  You don t have to turn it off if you use  GROUP BY properly  Example  Table  users  id      name ----------------   1      ali   2      john   3      ali  When you use GROUP BY on the name column  SELECT   FROM users GROUP BY name   There are two possible results    1      ali   2      john       OR   2      john   3      ali  MYSQL does not know what result to choose  Because there are different ids but both have name ali  Solution1  only selecting the name field  SELECT name FROM users GROUP BY name   result    ali   john       This is a perfect solution  removing columns that makes GROUP BY confused  This means you know what you re doing  Usually  you do not need those columns  but if you need them  go to solution3  Solution2  Turning off only full group by  MYSQL will show you one of the two possible results RANDOMLY    It s ok if you do not really care what id it will choose  Solution3 Use an Aggregate function like MIN    MAX   to help MYSQL to decide what it must choose  For example  I want the minimum id  SELECT MIN id   name FROM users GROUP BY name   result    1      ali   2      john       It will choose the ali row which has the minimum id

User · Answer

Search for  SQL mode  if you are using PhpMyAdmin and take off the value  ONLY FULL GROUP BY  just did and it okay

User · Answer

Update for MySQL 8 0  Your sql-mode will not have NO AUTO CREATE USER as it has been removed as mentioned here - how-to-set-sql-mode-in-my-cnf-in-mysql-8   mysqld  sql-mode    STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO ENGINE SUBSTITUTION    Also if someone doesn t have a my cnf file then they create a new one in  etc my cnf and then add the above lines

User · Answer

Open you  WAMP panel and open MySQL configuration file  In it search for  sql mode  if you find it set it to    else if you don t find it add sql mode    to the file   Restart the MySQL server and you are good to go     happy coding

User · Answer

Below method solved my problem   In ubuntu   Type  sudo vi  etc mysql my cnf  type A to enter insert mode  In the last line paste below two line code    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   Type esc to exit input mode  Type  wq to save and close vim    Type sudo service mysql restart to restart MySQL

User · Answer

For the query to be legal in SQL92  the name column must be omitted from the select list or named in the GROUP BY clause       SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns  If such a relationship exists between name and custid  the query is legal  This would be the case  for example  were custid a primary key of customers       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       via MySQL    MySQL 5 7 Reference Manual    12 19 3 MySQL Handling of GROUP BY   You can solve it by changing the sql mode with this command   SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY          and     remember to reconnect the database

User · Answer

Login to phpMyAdmin  Navigate to   Server  localhost 3306 and do not select any database Click on variables from the top menu Search for  sql mode  and edit the corresponding value to   NO ENGINE SUBSTITUTION   That s all   I did this in my Ec2 and it worked like charm

User · Answer

go to the phpmyadmin and open the console and execute this request  SET GLOBAL sql mode  SELECT REPLACE   sql mode  ONLY FULL GROUP BY

[mysql] SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Examples related to mysql

Examples related to group-by

Examples related to mysql-error-1055