---
title: "SQL GROUP BY - Complete Guide to Data Aggregation and Analysis"
description: "SQL GROUP BY groups rows by column values for aggregate calculations with COUNT, SUM, and AVG. Covers WHERE vs. HAVING, ROLLUP, GROUPING SETS, and performance."
url: "https://www.dawiso.com/glossary/sql-group-by-complete-guide-to-data-aggregation-and-analysis"
language: "en"
---

February 20, 2026

sql group bydata aggregationdatabase analyticssql grouping

# SQL GROUP BY - Complete Guide to Data Aggregation and Analysis

GROUP BY collapses rows that share a value into a single summary row. Combined with aggregate functions like [COUNT](https://www.dawiso.com/glossary/sql-count-complete-guide-to-counting-records-in-database-queries), [SUM, and AVG](https://www.dawiso.com/glossary/sql-sum-avg-min-max-complete-guide-to-sql-aggregate-functions), it turns raw transaction data into reports: revenue by region, orders by month, average salary by department. Most reporting queries rely on it.

TL;DR

**SQL GROUP BY** groups rows with identical values in specified columns and applies aggregate functions (COUNT, SUM, AVG, MIN, MAX) to each group. It returns one row per group. Use [WHERE](https://www.dawiso.com/glossary/sql-where-clause-complete-guide-to-data-filtering) to filter rows before grouping and HAVING to filter groups after aggregation. In standard SQL, every non-aggregated column in SELECT must appear in the GROUP BY clause (PostgreSQL and MySQL also accept columns that are functionally dependent on the grouped primary key); breaking this rule is one of the most common GROUP BY errors.

## How GROUP BY Works

Conceptually, GROUP BY works in three steps. First, the database takes the rows that survived the [WHERE](https://www.dawiso.com/glossary/sql-where-clause-complete-guide-to-data-filtering) filter. Second, it sorts or hashes those rows into **buckets** based on the GROUP BY columns - all rows with the same value in those columns land in the same bucket. Third, it runs the aggregate function on each bucket and produces **one output row per bucket**.

GROUP BY also has a fixed place in the **logical order** in which a query is evaluated: FROM (with its joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally TOP, as [Microsoft documents for SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql#logical-processing-order-of-the-select-statement) (LIMIT plays the role of TOP in other engines). That order explains most GROUP BY rules: WHERE cannot use an aggregate because no groups exist yet, and a column alias defined in SELECT is not yet available to the clauses before it in standard SQL. The order is logical, not physical; the optimizer may execute the steps differently as long as the result is the same.

The critical rule: **every column in SELECT must either appear in GROUP BY or be inside an aggregate function**. If you select `department` and `employee_name` but only group by `department`, the database has three employee names per group and no rule for which one to return, so [PostgreSQL](https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY), [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql) and [MySQL](https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html) (with its default `ONLY_FULL_GROUP_BY` mode, on since MySQL 5.7.5) reject the query. The SQL standard relaxes the rule for columns that are **functionally dependent** on the grouped columns: PostgreSQL accepts an ungrouped column when the primary key of its table is in GROUP BY, and MySQL also recognizes primary keys and unique NOT NULL columns. SQLite is the permissive outlier: it accepts these ["bare" columns](https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query) and takes their values from an arbitrary row of each group (or, when the query uses a single min() or max(), from the row that holds that minimum or maximum).

## GROUP BY with Aggregate Functions

The five core aggregate functions cover most reporting needs:

> `-- COUNT: how many orders per customer
> SELECT customer_id, COUNT(*) AS order_count
> FROM orders
> GROUP BY customer_id;
> -- SUM: total revenue by product category
> SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_revenue
> FROM order_items oi
> JOIN products p ON oi.product_id = p.product_id
> GROUP BY p.category;
> -- AVG: average order value by region
> SELECT region, AVG(total) AS avg_order_value
> FROM orders
> GROUP BY region;
> -- MIN / MAX: salary range by department
> SELECT department,
> MIN(salary) AS lowest_salary,
> MAX(salary) AS highest_salary
> FROM employees
> GROUP BY department;`

**Multi-column GROUP BY** creates finer granularity. Grouping by `category, region` produces one row for each unique category-region combination:

> `SELECT category, region,
> COUNT(*) AS transaction_count,
> SUM(amount) AS total_sales
> FROM sales
> GROUP BY category, region
> ORDER BY category, total_sales DESC;`

## WHERE vs HAVING

WHERE filters **rows** before grouping. HAVING filters **groups** after aggregation. They are not interchangeable:

> `-- Combined example: active employees only, departments with > 5 people
> SELECT department,
> COUNT(*) AS headcount,
> AVG(salary) AS avg_salary
> FROM employees
> WHERE status = 'active'
> GROUP BY department
> HAVING COUNT(*) > 5
> ORDER BY avg_salary DESC;`

**Why not just use HAVING for everything?** Because WHERE filters rows before the grouping work happens, reducing the dataset the database has to process. Filtering with HAVING after grouping can waste effort - the database may group rows only to throw them away. Some optimizers move a HAVING condition that uses no aggregate into WHERE on their own, but writing it in WHERE does not depend on that. Use WHERE for row-level conditions and HAVING exclusively for conditions on aggregate results.

WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. This is more efficient than adding the restriction to HAVING, because we avoid doing the grouping and aggregate calculations for all rows that fail the WHERE check.

\- PostgreSQL Documentation, [Aggregate Functions tutorial](https://www.postgresql.org/docs/current/tutorial-agg.html)

## Advanced Grouping

### GROUP BY with date functions

Date functions inside GROUP BY produce time-period reports. Monthly revenue, quarterly headcount, yearly churn - they all follow the same pattern:

> `-- Monthly revenue report (MySQL; SQL Server also has YEAR() and MONTH())
> SELECT YEAR(order_date) AS yr,
> MONTH(order_date) AS mo,
> SUM(total) AS monthly_revenue
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> ORDER BY yr, mo;
> -- PostgreSQL (and Snowflake) equivalent using DATE_TRUNC
> SELECT DATE_TRUNC('month', order_date) AS month,
> SUM(total) AS monthly_revenue
> FROM orders
> GROUP BY DATE_TRUNC('month', order_date)
> ORDER BY month;`

### Custom buckets with CASE

CASE inside GROUP BY lets you create arbitrary groupings that do not exist as columns in the data:

> `SELECT
> CASE
> WHEN total < 50 THEN 'Small (<$50)'
> WHEN total < 200 THEN 'Medium ($50-$199)'
> ELSE 'Large ($200+)'
> END AS order_size,
> COUNT(*) AS order_count,
> AVG(total) AS avg_value
> FROM orders
> GROUP BY
> CASE
> WHEN total < 50 THEN 'Small (<$50)'
> WHEN total < 200 THEN 'Medium ($50-$199)'
> ELSE 'Large ($200+)'
> END;`

Repeating the CASE expression in GROUP BY is the portable form. Some engines let you shorten it: [PostgreSQL](https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY) accepts the SELECT alias (`GROUP BY order_size`) or its position (`GROUP BY 1`), and [MySQL](https://dev.mysql.com/doc/refman/8.4/en/problems-with-alias.html) accepts the alias in GROUP BY and HAVING, while [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql) does not allow a SELECT-list alias in GROUP BY. None of the three accepts the alias in WHERE, and PostgreSQL does not accept it in HAVING either.

### ROLLUP, CUBE, and GROUPING SETS

These extensions produce multi-level aggregations in a single query. They are useful for reports that show both detail rows and totals.

**ROLLUP** adds subtotals and a grand total. The hierarchy follows the column order in the ROLLUP list:

> `-- MySQL WITH ROLLUP modifier (SQL Server keeps it as legacy syntax)
> SELECT category, region, SUM(amount) AS total_sales
> FROM sales
> GROUP BY category, region WITH ROLLUP;
> -- Standard syntax: PostgreSQL, SQL Server, Oracle, Snowflake (MySQL 8.4 also accepts it)
> SELECT category, region, SUM(amount) AS total_sales
> FROM sales
> GROUP BY ROLLUP(category, region);`

**GROUPING SETS** lets you define exactly which groupings to compute:

> `-- PostgreSQL, SQL Server, Oracle, Snowflake (not MySQL): three groupings in one query
> SELECT category, region, SUM(amount) AS total_sales
> FROM sales
> GROUP BY GROUPING SETS (
> (category),          -- totals by category
> (region),            -- totals by region
> ()                   -- grand total
> );`

**CUBE** computes every possible combination of the listed columns:

> `-- PostgreSQL, SQL Server, Oracle, Snowflake (not MySQL)
> SELECT category, region, SUM(amount) AS total_sales
> FROM sales
> GROUP BY CUBE(category, region);`

Subtotal rows mark the rolled-up columns with NULL, which is ambiguous when the data itself contains NULLs. The `GROUPING()` function resolves it: it returns 1 when the column was aggregated away in that row and 0 when the row carries a real grouping value ([PostgreSQL](https://www.postgresql.org/docs/current/functions-aggregate.html), [MySQL](https://dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html), [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql)).

> `SELECT category, region,
> GROUPING(category) AS is_category_total,
> GROUPING(region) AS is_region_total,
> SUM(amount) AS total_sales
> FROM sales
> GROUP BY ROLLUP(category, region);`

## Practical Patterns

### Top N categories

> `-- LIMIT: PostgreSQL, MySQL, SQLite, Snowflake
> -- SQL Server uses TOP; standard SQL uses FETCH FIRST 5 ROWS ONLY
> SELECT category, SUM(amount) AS total_sales
> FROM sales
> GROUP BY category
> ORDER BY total_sales DESC
> LIMIT 5;`

For the top N rows *within* each group, such as the three best-selling products per category, GROUP BY alone is not enough: rank the rows with `ROW_NUMBER()` over a [PARTITION BY](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning) window and filter on the rank.

### Percentage of total

Compute each category's share using a subquery or window function for the denominator:

> `SELECT category,
> SUM(amount) AS category_sales,
> ROUND(SUM(amount) * 100.0 / (SELECT SUM(amount) FROM sales), 1) AS pct_of_total
> FROM sales
> GROUP BY category
> ORDER BY category_sales DESC;`

The window-function form computes the denominator in the same pass: `SUM(SUM(amount)) OVER ()` adds up the per-category totals after grouping.

### Year-over-year comparison

> `-- MySQL 8.0+ / SQL Server functions; PostgreSQL uses EXTRACT()
> SELECT YEAR(order_date) AS yr,
> MONTH(order_date) AS mo,
> SUM(total) AS revenue,
> LAG(SUM(total)) OVER (
> PARTITION BY MONTH(order_date)
> ORDER BY YEAR(order_date)
> ) AS prev_year_revenue
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> ORDER BY yr, mo;`

The window function runs after GROUP BY, so `LAG(SUM(total))` reads the previous grouped row. It returns the previous year present in the data, so if a year is missing the comparison silently skips it.

### Groups above average

> `SELECT category, AVG(amount) AS avg_sale
> FROM sales
> GROUP BY category
> HAVING AVG(amount) > (SELECT AVG(amount) FROM sales)
> ORDER BY avg_sale DESC;`

This query compares each category's average to the average of all individual sales, not to the average of the category averages.

GROUP BY queries aggregate metrics like "total revenue" or "customer count", and those metrics need agreed-upon definitions. Dawiso's [business glossary](https://help.dawiso.com/actual/business-glossary/) holds the agreed definition of a business term, so teams can check what a `SUM(revenue)` in a report is supposed to include, for example whether refunds are netted out. Through the [data catalog](https://www.dawiso.com/glossary/data-catalog) and [data lineage](https://www.dawiso.com/glossary/data-lineage), analysts can see which source tables feed the table they are aggregating. Dawiso builds lineage from [supported connectors, dbt, SQL parsing (not on by default) and manually added relations](https://help.dawiso.com/actual/sources-of-data-lineage/), so how far back a trace reaches depends on which of those cover the pipeline.

## GROUP BY vs DISTINCT and Window Functions

**DISTINCT** removes duplicate result rows. `SELECT DISTINCT region FROM orders` returns the same rows as `SELECT region FROM orders GROUP BY region`, and optimizers often execute both the same way. Use GROUP BY when you need an aggregate per group, and DISTINCT when you only need the unique values.

**Window functions** compute across related rows without collapsing them. `SUM(salary) OVER (PARTITION BY department)` returns every employee row with the department total next to it, whereas GROUP BY returns one row per department. Window functions logically execute after GROUP BY and HAVING, which is why they may wrap an aggregate, as in the year-over-year example above, but cannot appear in WHERE, GROUP BY or HAVING ([PostgreSQL window functions tutorial](https://www.postgresql.org/docs/current/tutorial-window.html)).

## Performance

GROUP BY performance depends on how much data the database has to process and how it executes the grouping.

**Filter with WHERE first.** Rows removed by WHERE never reach the GROUP BY phase. A query that filters orders to the last quarter before grouping by category does much less work than one that aggregates every order first.

**Index for the filter and for the grouping.** An index whose leading columns match the GROUP BY columns can return rows already sorted by group, so the database can use a **sorted (streaming) aggregation** without a separate sort step. Otherwise it often uses a **hash aggregation**, which builds a hash table in memory. Column order in a composite index matters: an index led by the date column serves a date-range filter but returns rows in date order, not grouped by category.

> `-- Serves the WHERE range; rows come back in date order
> CREATE INDEX idx_orders_date ON orders(order_date);
> -- Led by the grouping column, so rows can be read in category order;
> -- including order_date and total lets some engines answer from the index alone
> CREATE INDEX idx_orders_category ON orders(category, order_date, total);
> SELECT category, SUM(total) AS revenue
> FROM orders
> WHERE order_date >= '2025-01-01'
> GROUP BY category;`

**Use EXPLAIN to check the strategy.** Which index, if any, the optimizer uses depends on how selective the filter is, so check the plan rather than assuming. In a PostgreSQL plan, look for `HashAggregate` (builds a hash table, typically chosen when the groups fit in memory) or `GroupAggregate` (needs input sorted on the grouping columns, typically chosen when an index or an earlier step already supplies that order). If you see a `Sort` node feeding `GroupAggregate`, an index led by the GROUP BY columns may remove it. SQL Server names the equivalent operators Hash Match and Stream Aggregate.

**Materialized views for repeated aggregations.** If the same GROUP BY query runs hourly for a dashboard, consider a materialized view that pre-computes the result and is refreshed on a schedule (in PostgreSQL, with `REFRESH MATERIALIZED VIEW`).

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. The memory limit for a hash table is computed by multiplying work_mem by hash_mem_multiplier, whose default value is 2.0.

\- PostgreSQL Documentation, [Resource Consumption: work_mem](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)

Hash aggregation needs memory roughly in proportion to the number of groups, and once it passes that limit the spill to disk slows the query. For queries with millions of distinct groups, raise the limit for that session or reduce the group count with WHERE.

## Common Errors

### Non-aggregate column not in GROUP BY

The single most common GROUP BY error. If you select a column that is not aggregated and not in GROUP BY, the database does not know which of the group's values to return:

> `-- ERROR in PostgreSQL, SQL Server, and MySQL with ONLY_FULL_GROUP_BY (the default)
> SELECT department, employee_name, SUM(salary)
> FROM employees
> GROUP BY department;
> -- Fix 1: add the column to GROUP BY (one row per employee)
> SELECT department, employee_name, SUM(salary) AS total_salary
> FROM employees
> GROUP BY department, employee_name;
> -- Fix 2: drop or aggregate the column (one row per department)
> SELECT department, COUNT(*) AS headcount, SUM(salary) AS total_salary
> FROM employees
> GROUP BY department;`

### MySQL with ONLY_FULL_GROUP_BY disabled

MySQL before 5.7.5, and current versions with `ONLY_FULL_GROUP_BY` removed from `sql_mode`, accept non-grouped columns and pick any value from each group. This produces **nondeterministic results** - the same query can return different employee names on different runs, and adding ORDER BY does not control which value is picked. Keep the mode enabled; when every row in a group really holds the same value, [`ANY_VALUE()`](https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html) states that intent explicitly.

### NULL values form their own group

Rows where the GROUP BY column is NULL are collected into a single group. This is standard behavior, but it surprises people who expect NULLs to be excluded. If you do not want a NULL group, filter them out with WHERE:

> `SELECT region, COUNT(*) AS order_count
> FROM orders
> WHERE region IS NOT NULL
> GROUP BY region;`

NULLs also behave differently inside aggregates. `COUNT(*)` counts every row in the group, while `COUNT(column)`, `SUM` and `AVG` skip NULL values ([PostgreSQL aggregate functions](https://www.postgresql.org/docs/current/functions-aggregate.html)). A group whose values are all NULL returns NULL from SUM, not 0, so wrap it in `COALESCE` if the report needs a zero.

### Confusing WHERE and HAVING

Using HAVING where WHERE would work is not an error, but it can be wasteful: logically, HAVING runs after grouping, so unless the optimizer moves the condition, the database groups rows it later discards. Use WHERE for row-level conditions (e.g., `WHERE status = 'active'`) and HAVING only when the condition involves an aggregate (e.g., `HAVING COUNT(*) > 5`).

## Sources

- PostgreSQL - [SELECT: GROUP BY and HAVING clauses](https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY)
- PostgreSQL - [Aggregate Functions tutorial](https://www.postgresql.org/docs/current/tutorial-agg.html)
- PostgreSQL - [Aggregate Functions (including GROUPING)](https://www.postgresql.org/docs/current/functions-aggregate.html)
- PostgreSQL - [Window Functions tutorial](https://www.postgresql.org/docs/current/tutorial-window.html)
- PostgreSQL - [Resource Consumption: work_mem and hash_mem_multiplier](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)
- MySQL - [MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html)
- MySQL - [GROUP BY Modifiers](https://dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html)
- MySQL - [Problems with Column Aliases](https://dev.mysql.com/doc/refman/8.4/en/problems-with-alias.html)
- Microsoft - [GROUP BY (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql)
- Microsoft - [SELECT (Transact-SQL): logical processing order](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql#logical-processing-order-of-the-select-statement)
- SQLite - [SELECT: bare columns in an aggregate query](https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query)
- Dawiso Help - [Business glossary](https://help.dawiso.com/actual/business-glossary/)
- Dawiso Help - [Sources of data lineage](https://help.dawiso.com/actual/sources-of-data-lineage/)

[See it in action](https://www.dawiso.com/product/data-catalog)

Data & Analytics Catalog

Create a unified view of your data assets and gain insights faster with automated data discovery.

### Table of contents

How GROUP BY WorksGROUP BY with Aggregate FunctionsWHERE vs HAVINGAdvanced GroupingPractical PatternsGROUP BY vs DISTINCT and Window FunctionsPerformanceCommon ErrorsSources

## Related Terms

### [Power BI AI Insights - Complete Guide to Intelligent Data Analysis](https://www.dawiso.com/glossary/power-bi-ai-insights-complete-guide-to-intelligent-data-analysis)

Unlock automated insights and predictive analytics with Power BI AI for smarter decisions

Read more →

### [What Is a Semantic Layer?](https://www.dawiso.com/glossary/semantic-layer)

Learn what a semantic layer is, how it works, and why it's critical for business-friendly analytics and AI-ready data.

Read more →

### [SQL INSERT - Comprehensive Guide to Database Data Insertion](https://www.dawiso.com/glossary/sql-insert-comprehensive-guide-to-database-data-insertion)

Master SQL INSERT statement for efficient database data insertion and record creation

Read more →

### [SQL PARTITION BY - Complete Guide to Window Functions and Data Partitioning](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning)

Learn SQL PARTITION BY for advanced window functions and data partitioning techniques

Read more →

### [SQL WHERE Clause - Complete Guide to Data Filtering](https://www.dawiso.com/glossary/sql-where-clause-complete-guide-to-data-filtering)

Master SQL WHERE clause for precise data filtering and conditional query logic

Read more →

### [What Is Decision Intelligence?](https://www.dawiso.com/glossary/decision-intelligence)

Decision intelligence is the discipline of engineering how decisions are made, combining data, analytics, and AI. Learn how it works and why governed data is its foundation.

Read more →
