How to Disable ONLY_FULL_GROUP_BY in MySQL?

In MySQL, the 'ONLY_FULL_GROUP_BY' mode ensures that queries with GROUP BY clauses are written according to SQL standards. This mode requires all columns in the SELECT statement that are not aggregated to be included in the GROUP BY clause. While this is helpful for ensuring data integrity, there are scenarios where you might need to disable this mode for more flexible query writing.

This article will guide you through the process of disabling 'ONLY_FULL_GROUP_BY' and provide examples with table creation, queries, and explanations.

ONLY_FULL_GROUP_BY

In simple terms, 'ONLY_FULL_GROUP_BY' is a setting in MySQL that ensures you write your GROUP BY queries correctly. It requires that every column in your SELECT statement, that isn't used with an aggregate function (like SUM, COUNT, AVG, etc.), must also appear in the GROUP BY clause. This rule helps prevent ambiguous and potentially incorrect results by making sure that you specify exactly how to group and summarize your data.

Steps to Disable ONLY_FULL_GROUP_BY

1. Check Current SQL Mode: First, check the current SQL modes enabled.
   
2. Disable ONLY_FULL_GROUP_BY: Modify the SQL mode to exclude ONLY_FULL_GROUP_BY.
   
3. Verify the Change: Ensure that the mode has been disabled.

Step-by-Step Guide to Disabling ONLY_FULL_GROUP_BY in MySQL

1. Check Current SQL Mode

To check the current SQL mode, run the following query:

SELECT @@sql_mode;

2. Disable ONLY_FULL_GROUP_BY

You can disable 'ONLY_FULL_GROUP_BY' by modifying the SQL mode configuration. This can be done temporarily for the current session or globally for all sessions.

Temporarily Disable for Current Session:

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

Permanently Disable for All Sessions: To disable it permanently, you need to edit the MySQL configuration file (my.cnf or my.ini) and remove 'ONLY_FULL_GROUP_BY' from the SQL mode line. Add or modify the following line under the [mysqld] section:

[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"

Then restart the MySQL service for the changes to take effect.

3. Verify the Change

To ensure that 'ONLY_FULL_GROUP_BY' has been disabled, run:

SELECT @@sql_mode;

Ensure that 'ONLY_FULL_GROUP_BY' is not in the result.

Example of MySQL Only_Full_Group_By

Example 1: Without ONLY_FULL_GROUP_BY

Create a Sample Table

Let's create a sample table sales to demonstrate the effect of disabling 'ONLY_FULL_GROUP_BY'.

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO sales (product, amount, sale_date) VALUES
('Product A', 100.00, '2024-06-01'),
('Product A', 150.00, '2024-06-02'),
('Product B', 200.00, '2024-06-01'),
('Product B', 250.00, '2024-06-03');

Query Without ONLY_FULL_GROUP_BY

Now, execute a query that selects non-aggregated columns not included in the GROUP BY clause:

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

SELECT product, amount FROM sales GROUP BY product;

Output:

+----------+--------+
| product  | amount |
+----------+--------+
| Product A| 100.00 |
| Product B| 200.00 |
+----------+--------+

Explanation: Without 'ONLY_FULL_GROUP_BY', MySQL returns one row per product with arbitrary values for amount (e.g., 100.00 for 'Product A' and 200.00 for 'Product B'), as the amount is not aggregated.

Example 2: With Aggregated Functions

Query with Aggregated Functions

Let's run a query that makes sense both with and without 'ONLY_FULL_GROUP_BY'.

SELECT product, SUM(amount) AS total_amount FROM sales GROUP BY product;

Output:

+----------+-------------+
| product  | total_amount|
+----------+-------------+
| Product A|  250.00     |
| Product B|  450.00     |
+----------+-------------+

Explanation: The query uses SUM(amount) to aggregate the amount for each product. Product A totals 250.00 (100.00 + 150.00), and Product B totals 450.00 (200.00 + 250.00), providing clear and accurate grouped results.

Conclusion

In Conclusion, the "only_full_group_by" error in MySQL acts as a protective measure to ensure that SQL standards are followed when using the GROUP BY clause. Although it may feel limiting, this setting guarantees precise and reliable query outcomes. When developers come across this error, they should carefully examine their queries to confirm that all non-aggregated columns in the SELECT list are also included in the GROUP BY clause. By doing this, they not only fix the error but also improve the clarity and correctness of their SQL queries, which contributes to creating a strong and compliant database environment.