intermediateCore SQLMySQL Specific100% Free

GROUP BY in MySQL

Definition

MySQL GROUP BY aggregates rows with identical values and, in strict mode (ONLY_FULL_GROUP_BY), requires all non-aggregated SELECT columns to be in the GROUP BY clause.

Introduction to GROUP BY in MySQL

MySQL's GROUP BY behavior has evolved significantly. MySQL 5.7+ enables ONLY_FULL_GROUP_BY by default, enforcing SQL standard GROUP BY rules. MySQL also offers GROUP BY ... WITH ROLLUP for hierarchical subtotals, a powerful reporting feature not available in all databases.

Syntax

SQL

SELECT col, AGG(val)
FROM table
GROUP BY col;

-- With ROLLUP for subtotals
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title WITH ROLLUP;

Examples

GROUP BY WITH ROLLUP

SELECT
  COALESCE(department, 'COMPANY TOTAL') AS dept,
  COALESCE(job_title, 'ALL TITLES') AS title,
  COUNT(*) AS headcount,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title WITH ROLLUP;

ROLLUP generates subtotal rows automatically. NULL values in the result indicate rollup rows — COALESCE gives them meaningful labels.

Try in Playground

Common Mistakes

Getting 'this is incompatible with sql_mode=only_full_group_by' error — must include all non-aggregate columns in GROUP BY

Using GROUP BY column position (GROUP BY 2) — works but brittle when SELECT list changes

ROLLUP NULLs confusing real NULL data — use GROUPING() function to distinguish them in MySQL 8.0+

Frequently Asked Questions

What is ONLY_FULL_GROUP_BY mode in MySQL?

ONLY_FULL_GROUP_BY is a SQL mode (enabled by default since MySQL 5.7) that enforces the SQL standard requirement that all non-aggregated columns in SELECT must appear in the GROUP BY clause. It prevents non-deterministic queries where MySQL would previously pick a random value for non-grouped columns.

What does GROUP BY WITH ROLLUP do in MySQL?

WITH ROLLUP generates additional summary rows with subtotals and grand totals. For GROUP BY a, b WITH ROLLUP, MySQL adds: a subtotal row per unique value of a (with NULL for b), and a grand total row (with NULL for both a and b). Use COALESCE or GROUPING() to give these rows meaningful labels.

Practice This in the SQL Playground

Write real queries, see live results, and master GROUP BY in MySQL hands-on. 100% free.

Open SQL Playground Browse All Lessons