ONLY_FULL_GROUP_BY: repair the report meaning

Understand the likely causes

• One group may contain several possible values for the selected column. Customer 1 has two orders with statuses paid and new, so customer_id does not determine one order status.
• Joining detail rows can also multiply parent totals. Fixing the grouping error alone does not repair an incorrectly chosen input grain or a mismatched aggregation population.

Inspect the active mode and declare the grain

This guide targets MySQL 8.4 with ONLY_FULL_GROUP_BY enabled. SELECT @@SESSION.sql_mode is a read-only inspection. Write the intended output grain in plain language before changing SQL: one row per customer, per customer/status pair, or per order. The correct repair depends on that answer, not on making the error vanish.

Group each category the report actually needs

The standalone fixture below has the same three synthetic orders as the course. Grouping by customer and status is appropriate when the report asks for each customer's value in each status. The selected category columns are explicit grouping keys, so there is no ambiguity and no SQL-mode change.

WITH sample_orders AS (
 SELECT 101 AS id,1 AS customer_id,'paid' AS status,25.00 AS total
 UNION ALL SELECT 102,1,'new',30.00
 UNION ALL SELECT 103,2,'paid',10.00
)
SELECT customer_id,status,SUM(total) AS amount
FROM sample_orders GROUP BY customer_id,status
ORDER BY customer_id,status;

customer_id | status | amount
1 | new | 30.00
1 | paid | 25.00
2 | paid | 10.00

Aggregate a defined fact for one row per customer

If the requirement is paid value per customer, use conditional aggregation rather than selecting an arbitrary status. If the requirement is the latest order's status, rank orders by placed_at and unique id in a subquery and select the first; MAX(status) means lexical maximum, not latest. ANY_VALUE is suitable only when any group value is truly acceptable and its arbitrariness is documented.

WITH sample_orders AS (
 SELECT 1 AS customer_id,'paid' AS status,25.00 AS total
 UNION ALL SELECT 1,'new',30.00
 UNION ALL SELECT 2,'paid',10.00
)
SELECT customer_id,
 SUM(CASE WHEN status='paid' THEN total ELSE 0 END) AS paid_value
FROM sample_orders GROUP BY customer_id ORDER BY customer_id;

customer_id | paid_value
1 | 25.00
2 | 10.00

Use real functional dependencies and safe totals

Grouping a customer primary key can make its stored name functionally dependent in MySQL. Grouping a nonunique name does not prove the same dependency. Preserve primary and unique keys rather than inferring identity from current sample coincidences. Before summing joined order totals, check whether order lines repeat each order; preaggregate detail or aggregate at the proper relation level.

Keep it from recurring

• Keep ONLY_FULL_GROUP_BY enabled in development and deployment, document each report's grain, and review NULL behavior and join multiplication. Verify MariaDB separately because functional-dependence detection and grouping behavior need not match MySQL.