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, SUM, and AVG, it turns raw transaction data into reports: revenue by region, orders by month, average salary by department. Most reporting queries rely on it.

How GROUP BY Works

Conceptually, GROUP BY works in three steps. First, the database takes the rows that survived the WHERE 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 (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, SQL Server and MySQL (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 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.

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 accepts the SELECT alias (GROUP BY order_size) or its position (GROUP BY 1), and MySQL accepts the alias in GROUP BY and HAVING, while SQL Server 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, MySQL, SQL Server).

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 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 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 and 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, 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).

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).

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() 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). 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
• PostgreSQL - Aggregate Functions tutorial
• PostgreSQL - Aggregate Functions (including GROUPING)
• PostgreSQL - Window Functions tutorial
• PostgreSQL - Resource Consumption: work_mem and hash_mem_multiplier
• MySQL - MySQL Handling of GROUP BY
• MySQL - GROUP BY Modifiers
• MySQL - Problems with Column Aliases
• Microsoft - GROUP BY (Transact-SQL)
• Microsoft - SELECT (Transact-SQL): logical processing order
• SQLite - SELECT: bare columns in an aggregate query
• Dawiso Help - Business glossary
• Dawiso Help - Sources of data lineage