How to Disable ONLY_FULL_GROUP_BY in MySQL?

The “ONLY_FULL_GROUP_BY” error in MySQL arises when executing a query that involves the GROUP BY clause but doesn’t follow the SQL standard for grouping. This strict mode, enabled by default, requires all columns in the SELECT list not part of an aggregate function to be included in the GROUP BY clause.

This article will explore the syntax leading to the “ONLY_FULL_GROUP_BY” error, provide examples to generate the error, and discuss solutions to address it.

Error Related to Only_Full_Group_By

The error occurs when using the GROUP BY clause without including all non-aggregated columns in the SELECT list in the GROUP BY clause. The basic syntax causing the issue looks like this:

SELECT column1, column2, aggregate_function(column3)
FROM your_table
GROUP BY column1;

Example of MySQL Only_Full_Group_By

Example 1: Generating “only_full_group_by” Error with Improper Query

Consider a scenario where we attempt to retrieve the total sales for each product category without including the product name in the GROUP BY clause.

-- Schema and Sample Data
CREATE TABLE sales (
    product_id INT,
    category VARCHAR(50),
    amount INT
);

INSERT INTO sales (product_id, category, amount)
VALUES 
(1, 'Electronics', 500),
(2, 'Electronics', 700),
(3, 'Clothing', 300),
(4, 'Clothing', 400);

-- Improper Query (Causing Error)
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

Output:

Output

Explanation: The corrected query groups the ‘sales‘ table by the ‘category‘ column and calculates the total ‘amount’ for each category using the SUM function. The output displays two rows, one for ‘Electronics’ with a total amount of 1200 and another for ‘Clothing’ with a total amount of 700.

Example 2: Proper Query with GROUP BY Clause

To avoid the error, we include all non-aggregated columns in the GROUP BY claus.

-- SQL Code
-- Create Database and Switch
CREATE DATABASE Increment;
USE Increment;

-- Schema and Sample Data
CREATE TABLE sales (
    product_id INT,
    category VARCHAR(50),
    amount INT
);

INSERT INTO sales (product_id, category, amount)
VALUES 
(1, 'Electronics', 500),
(2, 'Electronics', 700),
(3, 'Clothing', 300),
(4, 'Clothing', 400);

-- Proper Query
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

Output:

Output

Explanation: The output of the query presents a summary of sales data grouped by categories. It includes two rows: ‘Electronics’ with a total amount of 1200 and ‘Clothing‘ with a total amount of 700, reflecting the combined sales amounts for each respective product category from the ‘sales‘ table.

Conclusion

So, Overall, the “only_full_group_by” error in MySQL serves as a safeguard to enforce strict adherence to SQL standards when using the GROUP BY clause. While it may seem restrictive, this mode ensures accurate and predictable query results. Developers encountering this error should carefully review their queries, ensuring that all non-aggregated columns in the SELECT list are included in the GROUP BY clause. By doing so, they not only resolve the error but also enhance the clarity and correctness of their SQL queries, contributing to a robust and standards-compliant database environment.