How to Use GROUP BY in MySQL Database

I didn’t really understand GROUP BY until I had to build my first sales dashboard. My manager asked for “total revenue per region, per month,” and I sat there trying to write a query that would somehow collapse thousands of individual transaction rows into a neat little summary table. That’s the day GROUP BY stopped being a syntax I memorized for exams and became a tool I reach for almost daily. I want to walk through it the same way I eventually understood it — starting from what it actually does under the hood.

What GROUP BY Does

GROUP BY takes a result set and collapses rows that share the same value in one or more columns into a single summary row, letting you apply aggregate functions — SUM(), COUNT(), AVG(), MIN(), MAX() — to each group instead of the whole table. I like to describe it as “sorting into buckets, then measuring each bucket.”

Sample Schema

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50),
    salesperson VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE
);

INSERT INTO sales (region, salesperson, amount, sale_date) VALUES
('North', 'Ayesha', 500.00, '2026-01-05'),
('North', 'Bilal', 300.00, '2026-01-15'),
('South', 'Ayesha', 700.00, '2026-01-20'),
('South', 'Sara', 200.00, '2026-02-02'),
('North', 'Ayesha', 150.00, '2026-02-10'),
('South', 'Sara', 400.00, '2026-02-18');

Basic GROUP BY

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Output:

+--------+-------------+
| region | total_sales |
+--------+-------------+
| North  |      950.00 |
| South  |     1300.00 |
+--------+-------------+

MySQL scanned every row, bucketed them by region, and summed amount within each bucket. Every column in the SELECT list that isn’t wrapped in an aggregate function must appear in the GROUP BY clause — MySQL enforces this under ONLY_FULL_GROUP_BY mode, which has been the default since MySQL 5.7.

Grouping by Multiple Columns

SELECT region, salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson;

Output:

+--------+-------------+-------------+
| region | salesperson | total_sales |
+--------+-------------+-------------+
| North  | Ayesha      |      650.00 |
| North  | Bilal       |      300.00 |
| South  | Ayesha      |      700.00 |
| South  | Sara        |      600.00 |
+--------+-------------+-------------+

This creates a bucket for every unique combination of region and salesperson — notice Ayesha appears twice because she sold in both regions.

GROUP BY with WHERE and HAVING

This is the distinction that confused me the longest: WHERE filters rows before grouping, and HAVING filters groups after aggregation.

SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 1000;

Output:

+--------+-------------+
| region | total_sales |
+--------+-------------+
| South  |     1300.00 |
+--------+-------------+

I couldn’t have written WHERE SUM(amount) > 1000 — MySQL rejects aggregate functions inside WHERE because, at the point WHERE executes, aggregation hasn’t happened yet. HAVING exists specifically to filter on the result of an aggregate.

Grouping with Date Functions

A pattern I use constantly for monthly reports:

SELECT DATE_FORMAT(sale_date, '%Y-%m') AS sale_month, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY sale_month;

Output:

+------------+-------------+
| sale_month | total_sales |
+------------+-------------+
| 2026-01    |     1500.00 |
| 2026-02    |      750.00 |
+------------+-------------+

ROLLUP for Subtotals

WITH ROLLUP adds subtotal and grand-total rows automatically, which saved me from writing manual UNION queries for summary reports.

SELECT region, salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY region, salesperson WITH ROLLUP;

Output:

+--------+-------------+-------------+
| region | salesperson | total_sales |
+--------+-------------+-------------+
| North  | Ayesha      |      650.00 |
| North  | Bilal       |      300.00 |
| North  | NULL        |      950.00 |
| South  | Ayesha      |      700.00 |
| South  | Sara        |      600.00 |
| South  | NULL        |     1300.00 |
| NULL   | NULL        |     2250.00 |
+--------+-------------+-------------+

The NULL rows represent subtotals (per region) and a final grand total. I use GROUPING() to distinguish a “real” NULL value in the data from a rollup-generated NULL, which matters if the grouped column itself can legitimately contain NULL.

How GROUP BY Executes Internally

Internally, MySQL has two primary strategies for grouping:

1. Using an index — if there’s an index on the GROUP BY column(s) matching the query’s order, MySQL can walk the index in order and group rows as it goes, without a separate sorting step. This shows up in EXPLAIN as “Using index for group-by.”
2. Using a temporary table with sorting — if no useful index exists, MySQL creates an internal temporary table, sorts the rows (or uses a hash-based grouping in newer versions), and then aggregates. This shows up as “Using temporary; Using filesort,” and it’s noticeably slower on large tables.

flowchart TD
    A[GROUP BY Query] --> B{Index available matching GROUP BY columns?}
    B -->|Yes| C[Loose or tight index scan]
    C --> D[Group rows directly from index order]
    B -->|No| E[Create temporary table]
    E --> F[Sort or hash rows by group key]
    F --> G[Aggregate each group]

GROUP BY vs. Window Functions

Since MySQL 8.0, window functions overlap conceptually with GROUP BY but solve a different problem: GROUP BY collapses rows into one row per group, while window functions (OVER()) let you compute aggregates without collapsing rows, so you still see every original row alongside its group’s total.

SELECT region, salesperson, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

I use GROUP BY when I want a compact summary, and window functions when I need row-level detail alongside a group-level metric — for example, “what percentage of this region’s total did this specific sale represent.”

Real-World DBA and Reporting Scenarios

• Dashboards: nearly every KPI widget I’ve built — revenue by region, signups by day, error counts by service — starts life as a GROUP BY query.
• Deduplication analysis: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; is my go-to first step before writing a proper deduplication migration.
• Capacity planning: grouping log tables by hour or by endpoint helps me spot traffic patterns before scaling infrastructure.
• Data quality audits: grouping by a foreign key and checking for unexpectedly large or small group sizes often reveals bad joins or missing filters upstream.

Common Mistakes

• Forgetting ONLY_FULL_GROUP_BY compliance — selecting a non-aggregated, non-grouped column and getting either an error or (on older MySQL with the mode disabled) an arbitrary, non-deterministic value for that column.
• Using WHERE when HAVING was needed, or vice versa, leading to either a syntax error or silently wrong results if WHERE is applied to a column that doesn’t exist pre-aggregation.
• Grouping by a computed expression in SELECT but writing a slightly different expression in GROUP BY, which MySQL treats as a different, unmatched group.
• Assuming GROUP BY result order is guaranteed — it isn’t; always add an explicit ORDER BY if row order matters for your report.

Best Practices

• Always add an explicit ORDER BY after GROUP BY if the consuming application or report depends on row order.
• Index the columns you frequently group by, especially for large fact tables — this can turn a filesort-heavy query into a fast index scan.
• Use HAVING sparingly and only for conditions on aggregated values; push everything else into WHERE so it filters before the expensive aggregation step.
• When debugging unexpected group counts, run the query without the aggregate first (SELECT DISTINCT grouping_column FROM table) to sanity-check the actual distinct values.

Troubleshooting Table

FAQs

Can I use GROUP BY without any aggregate function? Yes — SELECT region FROM sales GROUP BY region; simply returns distinct regions, though SELECT DISTINCT region FROM sales; is the clearer way to express that specific intent.

What is ONLY_FULL_GROUP_BY? It’s a SQL mode (default since MySQL 5.7) that rejects queries selecting a non-aggregated column that isn’t part of the GROUP BY clause, preventing non-deterministic results.

Does GROUP BY guarantee sorted output? No. Historically it often appeared sorted as a side effect of the grouping algorithm, but this was never guaranteed and shouldn’t be relied on — always add ORDER BY explicitly.

What’s the difference between GROUP BY and DISTINCT? DISTINCT removes duplicate rows from the result set; GROUP BY is built for aggregating values within buckets, though for simple “list unique values” cases they can produce similar output.

Can I group by a column alias? Yes, MySQL allows referencing a SELECT-list alias in GROUP BY (and HAVING/ORDER BY), unlike some strict SQL dialects.

Interview Questions

1. What’s the execution order of WHERE, GROUP BY, HAVING, and ORDER BY in a query?
2. Why does ONLY_FULL_GROUP_BY mode exist, and what problem does it prevent?
3. How would you get subtotal and grand-total rows in a single query?
4. What’s the difference between filtering with WHERE versus HAVING?
5. How can an index improve GROUP BY performance?
6. When would you use a window function instead of GROUP BY?
7. How do you find duplicate values in a column using GROUP BY?

Optimization Tips

• Create composite indexes matching your most common GROUP BY column order to allow index-based grouping instead of temporary-table sorting.
• Filter as much as possible in WHERE before grouping — reducing row count before aggregation is almost always cheaper than aggregating everything and filtering after.
• Use EXPLAIN to check for “Using temporary; Using filesort” and treat it as a signal to reconsider indexing or query structure.
• For very large tables, consider pre-aggregating into a summary table refreshed on a schedule, rather than recomputing GROUP BY aggregates on every dashboard load.

Summary and Key Takeaways

GROUP BY is the tool that turns raw transactional rows into meaningful summaries — totals, averages, counts — bucketed by whatever dimension matters to the question being asked. The keys I keep coming back to: filter before grouping with WHERE, filter after grouping with HAVING, always add an explicit ORDER BY if order matters, and check EXPLAIN on large tables to make sure MySQL isn’t falling back to a slow temporary-table sort. Once grouping and aggregation are second nature, most reporting and dashboard work becomes a matter of picking the right dimensions and metrics rather than fighting the syntax.

References

• MySQL 8.0 Reference Manual — GROUP BY Modifiers
• MySQL 8.0 Reference Manual — MySQL Handling of GROUP BY
• MySQL 8.0 Reference Manual — GROUP BY Optimization