DEV Community

Manoj Swami
Manoj Swami

Posted on

MySQL ONLY_FULL_GROUP_BY Error: Easy Fixes and Best Practices

Have you ever seen this MySQL error and felt confused?

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Enter fullscreen mode Exit fullscreen mode

Don't worry! This guide will help you understand and fix this error. Whether you're new to MySQL or an experienced user, you'll find helpful solutions here.

What is ONLY_FULL_GROUP_BY?

ONLY_FULL_GROUP_BY is a MySQL setting that makes GROUP BY work in a standard way. When it's on, your SELECT statement must follow these rules:

  • Include all columns in the GROUP BY part
  • Use functions like COUNT or SUM for other columns
  • Only use columns that depend on the GROUP BY columns

Why Does This Error Happen?

Let's look at an example. Imagine you have a table called orders:

CREATE TABLE orders (
    id INT,
    customer_name VARCHAR(100),
    product VARCHAR(100),
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 'John', 'Laptop', 1000),
(2, 'John', 'Mouse', 20),
(3, 'Mary', 'Keyboard', 50),
(4, 'Mary', 'Monitor', 200);
Enter fullscreen mode Exit fullscreen mode

This query will cause the error:

SELECT customer_name, product, SUM(amount)
FROM orders
GROUP BY customer_name;
Enter fullscreen mode Exit fullscreen mode

Why? Because we're selecting product, but it's not in the GROUP BY part. MySQL doesn't know which product to show for each customer since they might have bought multiple products.

How to Fix the Error

Fix 1: Change MySQL Settings

For a quick fix, you can turn off ONLY_FULL_GROUP_BY:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Enter fullscreen mode Exit fullscreen mode

For a permanent fix:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Enter fullscreen mode Exit fullscreen mode

Fix 2: Change Your Queries

Method 1: Add All Columns to GROUP BY

SELECT customer_name, product, SUM(amount)
FROM orders
GROUP BY customer_name, product;
Enter fullscreen mode Exit fullscreen mode

Method 2: Use Functions Like MAX or MIN

SELECT 
    customer_name,
    MAX(product) as product,
    SUM(amount) as total_amount
FROM orders
GROUP BY customer_name;
Enter fullscreen mode Exit fullscreen mode

Method 3: Use Subqueries

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT customer_name, SUM(amount) as total_amount
    FROM orders
    GROUP BY customer_name
) grouped ON o.customer_name = grouped.customer_name;
Enter fullscreen mode Exit fullscreen mode

Real-World Examples

Example 1: Sales Report

SELECT 
    category,
    MAX(product_name) as top_product,
    COUNT(*) as total_orders,
    SUM(amount) as total_sales
FROM sales
GROUP BY category;
Enter fullscreen mode Exit fullscreen mode

Example 2: Customer Analysis

SELECT 
    customer_id,
    MIN(first_name) as first_name,
    MIN(last_name) as last_name,
    COUNT(*) as total_purchases,
    SUM(purchase_amount) as total_spent,
    AVG(purchase_amount) as avg_purchase_amount
FROM customer_purchases
GROUP BY customer_id;
Enter fullscreen mode Exit fullscreen mode

Common Mistakes to Avoid

  1. Forgetting About Unique IDs
   -- Wrong
   SELECT id, customer_name, COUNT(*)
   FROM orders
   GROUP BY customer_name;

   -- Right
   SELECT MIN(id) as first_order_id, customer_name, COUNT(*)
   FROM orders
   GROUP BY customer_name;
Enter fullscreen mode Exit fullscreen mode
  1. Complex Joins with GROUP BY
   -- Wrong
   SELECT o.id, c.name, p.category, COUNT(*)
   FROM orders o
   JOIN customers c ON o.customer_id = c.id
   JOIN products p ON o.product_id = p.id
   GROUP BY c.name;

   -- Right
   SELECT 
       MIN(o.id) as order_id,
       c.name,
       GROUP_CONCAT(DISTINCT p.category) as categories,
       COUNT(*) as total_orders
   FROM orders o
   JOIN customers c ON o.customer_id = c.id
   JOIN products p ON o.product_id = p.id
   GROUP BY c.name;
Enter fullscreen mode Exit fullscreen mode

Best Practices

  1. Think about what makes sense for your data
  2. Use functions like SUM or AVG that give useful information
  3. Explain your choices in comments

Troubleshooting Tips

  1. Check your current MySQL settings:
   SELECT @@sql_mode;
Enter fullscreen mode Exit fullscreen mode
  1. Make sure your changes worked:
   SHOW VARIABLES LIKE 'sql_mode';
Enter fullscreen mode Exit fullscreen mode
  1. Check what you're allowed to do:
   SHOW GRANTS;
Enter fullscreen mode Exit fullscreen mode

When to Keep ONLY_FULL_GROUP_BY On

It's tempting to turn off ONLY_FULL_GROUP_BY, but consider keeping it on if:

  • You're starting a new project
  • You need to follow SQL standards
  • You want to avoid small mistakes in your queries

Conclusion

The ONLY_FULL_GROUP_BY error can be annoying, but it helps us write better MySQL queries. Instead of just turning it off, try to understand why it exists and how to work with it. This will make you a better developer and help you write MySQL code that's easier to maintain.

Remember:

  1. Think about what data you really need
  2. Use the right functions like SUM or AVG
  3. Group by all important columns
  4. Consider keeping ONLY_FULL_GROUP_BY on for better code

Happy Coding!

👋 While you are here

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay