Home/Learn/MySQL/GROUP BY & Aggregation

GROUP BY & Aggregation

Beginner

Joins & Set Ops

GROUP BY collapses rows by distinct values; aggregate functions (COUNT, SUM, AVG, MIN, MAX) operate per group; ROLLUP and CUBE extend GROUP BY for hierarchical totals.

Overview

GROUP BY collapses multiple rows into a single output row per unique combination of grouped columns, enabling aggregate functions (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT) to summarise each group. MySQL's ONLY_FULL_GROUP_BY mode (enabled by default since 5.7) enforces that every column in SELECT must either be in GROUP BY or be wrapped in an aggregate function. HAVING filters groups after aggregation, unlike WHERE which filters individual rows before. WITH ROLLUP adds subtotal and grand total rows to the output for hierarchical reporting. Combining GROUP BY with window functions (MySQL 8.0+) enables both aggregated groups and per-row detail in the same query.

Basic GROUP BY with aggregate functions

Standard aggregations for reporting — count, sum, average per group with HAVING filter.

SQL — GROUP BY with standard aggregate functions

-- Revenue and order count by status
SELECT
    status,
    COUNT(*)                            AS order_count,
    SUM(total)                          AS revenue,
    AVG(total)                          AS avg_order_value,
    MIN(total)                          AS min_order,
    MAX(total)                          AS max_order,
    GROUP_CONCAT(id ORDER BY id LIMIT 5) AS sample_ids
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status
HAVING COUNT(*) >= 10          -- filter groups with at least 10 orders
ORDER BY revenue DESC;

-- Multi-column grouping: revenue by month and category
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    category,
    COUNT(*)                          AS orders,
    SUM(total)                        AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY month, category
ORDER BY month, revenue DESC;

-- COUNT(DISTINCT): unique customer count per day
SELECT
    DATE(created_at)            AS day,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*)                    AS total_orders
FROM orders
GROUP BY day
ORDER BY day;

WITH ROLLUP for hierarchical subtotals

WITH ROLLUP appends subtotal rows for each GROUP BY level and a grand total row. Use GROUPING() to distinguish real NULL values from rollup-added NULL markers.

SQL — WITH ROLLUP + GROUPING() for reporting

-- Revenue by region and category with subtotals
SELECT
    COALESCE(region, 'ALL REGIONS')       AS region,
    COALESCE(category, 'ALL CATEGORIES')  AS category,
    SUM(total)                            AS revenue,
    COUNT(*)                              AS orders
FROM orders o
JOIN customers c  ON o.customer_id = c.id
JOIN products p   ON o.product_id  = p.id
GROUP BY region, category WITH ROLLUP;

-- Output:
-- | region  | category   | revenue | orders |
-- | APAC    | Electronics| 50000   | 200    |
-- | APAC    | Apparel    | 20000   | 150    |
-- | APAC    | NULL       | 70000   | 350    | ← region subtotal
-- | EMEA    | Electronics| 80000   | 300    |
-- | ...     |            |         |        |
-- | NULL    | NULL       | 200000  | 1000   | ← grand total

-- GROUPING() distinguishes real NULLs from ROLLUP NULLs
SELECT
    IF(GROUPING(region)   = 1, 'Grand Total', COALESCE(region, 'Unknown')) AS region,
    IF(GROUPING(category) = 1, 'Subtotal',   COALESCE(category, 'Other'))  AS category,
    SUM(total) AS revenue
FROM orders o JOIN customers c ON o.customer_id = c.id
              JOIN products p  ON o.product_id  = p.id
GROUP BY region, category WITH ROLLUP;

Common mistakes: ONLY_FULL_GROUP_BY and filtering

ONLY_FULL_GROUP_BY mode (default in MySQL 5.7+) enforces SELECT discipline. Filtering after aggregation requires HAVING, not WHERE.

SQL — ONLY_FULL_GROUP_BY fix and WHERE vs HAVING

-- ERROR: column not in GROUP BY and not aggregated
SELECT customer_id, email, COUNT(*)  -- email not in GROUP BY!
FROM orders o JOIN customers c ON o.customer_id = c.id
GROUP BY customer_id;
-- Error 1055: 'customers.email' is not functionally dependent on GROUP BY

-- FIX: include email in GROUP BY or use ANY_VALUE()
SELECT customer_id, ANY_VALUE(email), COUNT(*)
FROM orders o JOIN customers c ON o.customer_id = c.id
GROUP BY customer_id;

-- WHERE vs HAVING: common confusion
-- WHERE filters ROWS before grouping (can use indexes)
-- HAVING filters GROUPS after aggregation (cannot use regular indexes)

-- CORRECT: WHERE for row filter, HAVING for group filter
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'   -- ← filter rows first (uses index)
GROUP BY customer_id
HAVING revenue > 1000               -- ← filter groups after aggregation
ORDER BY revenue DESC
LIMIT 20;

Key Points to Remember

1ONLY_FULL_GROUP_BY (default MySQL 5.7+): every SELECT column must be in GROUP BY or wrapped in an aggregate.
2HAVING filters groups after aggregation; WHERE filters rows before — they are not interchangeable.
3COUNT(col) counts non-NULL values; COUNT(*) counts all rows — they differ when the column has NULLs.
4WITH ROLLUP adds subtotal and grand total rows; use GROUPING() to distinguish real NULLs from rollup markers.
5GROUP_CONCAT aggregates values into a comma-separated string per group; max length controlled by group_concat_max_len.
6Aggregation and window functions can be combined: aggregate with GROUP BY, then rank groups with window functions.

Interview Questions

What is the difference between WHERE and HAVING and when do you use each?

EasyAmazon

What does ONLY_FULL_GROUP_BY enforce and how do you fix violations?

MediumGoogle

What is the difference between COUNT(*) and COUNT(column_name)?

EasyNetflix

How does WITH ROLLUP work and how do you distinguish rollup-generated NULLs from real NULLs?

HardMeta

How would you find the top 3 customers by revenue in each region using GROUP BY?

HardUber

Ask Aria about GROUP BY & Aggregation

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Subqueries

HAVING Clause