MySQL GROUP BY and Aggregates

Also Known As

TL;DR

Explanation

GROUP BY is used with aggregate functions to summarise data. MySQL's ONLY_FULL_GROUP_BY mode (default since 5.7) requires all SELECT columns to be in GROUP BY or wrapped in an aggregate — preventing non-deterministic results. HAVING filters groups after aggregation; WHERE filters rows before aggregation. ROLLUP adds subtotals. Window functions (MySQL 8) provide aggregation without collapsing rows.

Common Misconception

Why It Matters

Common Mistakes

• Using WHERE to filter aggregate results instead of HAVING — WHERE runs before aggregation.
• Selecting non-aggregated columns not in GROUP BY — non-deterministic on MySQL permissive mode.
• GROUP BY on an unindexed column — triggers filesort in the EXPLAIN Extra column.

Avoid When

• Do not use GROUP BY on an unindexed column for large tables — add an index to avoid filesort.
• Do not use GROUP BY as a workaround for DISTINCT — they have different semantics.

When To Use

• Use GROUP BY with COUNT/SUM/AVG for reporting and analytics queries.
• Use HAVING to filter groups — e.g. users with more than 5 orders.

Code Examples

-- ONLY_FULL_GROUP_BY error in MySQL 5.7+
SELECT user_id, email, COUNT(*) AS orders
FROM orders
GROUP BY user_id; -- email not in GROUP BY or aggregate

SELECT user_id,
       COUNT(*)        AS order_count,
       SUM(total)      AS revenue,
       MAX(created_at) AS last_order
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY revenue DESC;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

Tags

Related Terms