select list is not in group by clause and contains nonaggregated column

Question

I am getting the error "select list is not in group by clause and contains nonaggregated column" whenever executing the SQL quries on my server, How can it be resolved?
 

Thank you

Answer 1

The database structure seen needs SQL Mode ONLY_FULL_GROUP_BY disabled
 Please refer to: How do I disable ONLY_FULL_GROUP_BY in MySQL?
 
Please also note this is a global MySQL setting and is not able to be set at a user level.

Answer 2

To disable the ONLY_FULL_GROUP_BY mode in MySQL, you can use the following steps:

1. Connect to your MySQL server: Open a terminal window and enter the command mysql -u username -p, replacing "username" with your MySQL username. When prompted, enter your MySQL password.
    
2. Open the MySQL configuration file: The MySQL configuration file is typically located at /etc/mysql/my.cnf or /etc/my.cnf. You can use the command nano /etc/mysql/my.cnf to open the file in the Nano text editor. If the file does not exist, you can create it.
    
3. Add the following line to the configuration file: sql_mode = ''
    
4. Save the changes to the configuration file and exit the text editor.
    
5. Restart the MySQL server: Use the command sudo service mysql restart to restart the MySQL server.
    
6. Verify that the ONLY_FULL_GROUP_BY mode is disabled: Connect to the MySQL server and enter the command SHOW VARIABLES LIKE 'sql_mode';. This should show the current value of the sql_mode variable, which should not include the ONLY_FULL_GROUP_BY mode.

Note: These instructions are for MySQL running on a Linux server. If you are using a different operating system, the steps may be slightly different.

Answer 3

To disable the ONLY_FULL_GROUP_BY mode in MySQL, you can use the following steps:

1. Open the MySQL configuration file (my.cnf or my.ini, depending on your operating system).
    

2. Add the following line to the [mysqld] section of the configuration file:

   sql_mode = ''

3. Save the changes to the configuration file and exit the text editor.
    
4. Restart the MySQL server for the changes to take effect.

Alternatively, you can also disable ONLY_FULL_GROUP_BY by running the following command in the MySQL client:

      SET sql_mode = '';

This will disable ONLY_FULL_GROUP_BY for the current MySQL session. To make the change permanent, you will need to modify the MySQL configuration file as described above.