GROUP BY was one of those SQL clauses I technically understood the syntax of long before I actually understood what it was doing under the hood. I could copy-paste a working GROUP BY query, but the moment I needed to write one from scratch for a new report, I’d get tangled up in errors about columns not appearing in the group. Once it finally clicked — once I understood that GROUP BY physically collapses rows into buckets before anything else happens — everything about aggregation queries got a lot easier. Let me walk you through it the way I wish someone had walked me through it.
What Does GROUP BY Actually Do?
GROUP BY takes your result set and collapses rows that share the same value in one or more specified columns into a single group. Once rows are grouped, you can apply aggregate functions (COUNT, SUM, AVG, MIN, MAX, and others) to each group independently, producing one output row per group.
Without GROUP BY, an aggregate function operates on the entire table as one giant group. With GROUP BY, you’re telling PostgreSQL to split the table into smaller groups first.
Setting Up an Example
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
region VARCHAR(50),
product VARCHAR(50),
amount NUMERIC(10,2),
sale_date DATE
);
INSERT INTO sales (region, product, amount, sale_date) VALUES
('North', 'Widget', 100.00, '2026-01-05'),
('North', 'Gadget', 200.00, '2026-01-10'),
('South', 'Widget', 150.00, '2026-01-12'),
('South', 'Widget', 90.00, '2026-02-02'),
('East', 'Gadget', 300.00, '2026-02-15');
Basic Syntax
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;
Result:
| region | total_sales |
|---|---|
| North | 300.00 |
| South | 240.00 |
| East | 300.00 |
PostgreSQL collapsed the five original rows into three groups — one per unique region value — and calculated the sum within each group.
The Rule That Trips Up Beginners
Every column in your SELECT list that isn’t wrapped in an aggregate function must appear in the GROUP BY clause. This is enforced strictly by PostgreSQL, and it’s the number one error people run into:
-- This will error
SELECT region, product, SUM(amount)
FROM sales
GROUP BY region;
PostgreSQL will reject this with something like: column "sales.product" must appear in the GROUP BY clause or be used in an aggregate function. The reasoning is straightforward — if you’re grouping only by region, PostgreSQL has no way to know which product value to display for a group that might contain multiple different products. You either need to add product to the GROUP BY, or wrap it in an aggregate like ARRAY_AGG(product).
The fix:
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY region, product;
Grouping by Multiple Columns
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY region, product
ORDER BY region, total_sales DESC;
Result:
| region | product | total_sales |
|---|---|---|
| East | Gadget | 300.00 |
| North | Gadget | 200.00 |
| North | Widget | 100.00 |
| South | Widget | 240.00 |
Now each unique combination of region and product gets its own group. This is a very common real-world pattern — breaking a total down along two or more dimensions at once.
Grouping by Expressions
You can group by the result of a function or expression, not just a raw column — this is incredibly useful for time-based reporting:
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;
This groups sales by calendar month rather than by exact date, giving me monthly totals instead of a separate row for every single day something sold. DATE_TRUNC() is one of the functions I use constantly for this kind of reporting.
You can also group using a column alias, which PostgreSQL supports in GROUP BY (unlike some other databases):
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
ORDER BY month;
GROUP BY With HAVING
HAVING filters groups after aggregation, unlike WHERE, which filters rows before grouping happens.
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 250;
This only shows regions whose total sales exceed $250. You cannot achieve this with WHERE, because at the point WHERE runs, the aggregation hasn’t happened yet — SUM(amount) doesn’t exist as a per-row value.
You can combine both in the same query, and PostgreSQL applies them in the correct logical order — WHERE first, then grouping, then HAVING:
SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 200
ORDER BY total_sales DESC;
GROUP BY With JOIN
GROUP BY works naturally alongside joins — you typically group by columns from the “one” side of a relationship while aggregating columns from the “many” side:
CREATE TABLE regions (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
manager VARCHAR(100)
);
SELECT r.name, r.manager, SUM(s.amount) AS total_sales
FROM regions r
JOIN sales s ON s.region = r.name
GROUP BY r.name, r.manager;
Notice both r.name and r.manager need to be in the GROUP BY since neither is wrapped in an aggregate function.
GROUP BY ALL Columns You Need — Not More Than Necessary
A subtle mistake I made early on: adding unnecessary columns to GROUP BY “just to be safe,” which fragments your groups more than intended. If manager is uniquely determined by region (a functional dependency), adding it to GROUP BY doesn’t change your results — but if it’s not uniquely determined, you’ll silently get more groups than you meant to, splitting data that should have been combined.
ROLLUP and CUBE — Subtotals and Grand Totals
PostgreSQL supports GROUP BY ROLLUP and GROUP BY CUBE for generating subtotal and grand-total rows automatically — genuinely useful for reporting.
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY region, product;
ROLLUP produces the normal grouped rows, plus subtotal rows for each region (with product as NULL), plus one grand total row (both NULL). This saves you from writing a UNION of three separate aggregation queries to get the same effect.
CUBE goes further, generating subtotals for every possible combination of the grouping columns:
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY CUBE (region, product);
I use ROLLUP far more often than CUBE in practice — most reports want a hierarchical subtotal structure, not every possible combination.
GROUPING SETS — Custom Combinations
If you need specific custom combinations rather than the full rollup or cube, GROUPING SETS lets you define exactly which groupings you want in one query:
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());
This produces the detailed rows, region subtotals, and a grand total — but skips a product-only subtotal, which ROLLUP or CUBE might otherwise include unnecessarily.
Combining GROUP BY With FILTER for Multi-Metric Reports
One pattern I use constantly once a report needs more than one conditional metric per group: combining GROUP BY with the FILTER clause instead of writing multiple separate queries.
SELECT
region,
COUNT(*) AS total_sales,
COUNT(*) FILTER (WHERE amount > 150) AS large_sales,
SUM(amount) FILTER (WHERE product = 'Widget') AS widget_revenue,
SUM(amount) AS total_revenue
FROM sales
GROUP BY region;
This produces four different metrics per region in a single grouped pass over the data, each with its own independent condition. Before I discovered FILTER, I’d have written this with nested CASE WHEN expressions inside each aggregate, which works but is noticeably harder to read at a glance:
SELECT
region,
COUNT(*) AS total_sales,
COUNT(CASE WHEN amount > 150 THEN 1 END) AS large_sales,
SUM(CASE WHEN product = 'Widget' THEN amount ELSE 0 END) AS widget_revenue,
SUM(amount) AS total_revenue
FROM sales
GROUP BY region;
Both produce identical results — FILTER is really just cleaner syntax for the same underlying idea, and I’d recommend it whenever your PostgreSQL version supports it (it’s been available since PostgreSQL 9.4, so this is safe in essentially any modern setup).
GROUP BY vs DISTINCT ON — Choosing the Right Tool
I mentioned briefly that GROUP BY and DISTINCT solve different problems, but there’s a third related tool worth knowing: PostgreSQL’s DISTINCT ON, which is genuinely different from both.
GROUP BY collapses rows and lets you compute aggregates across each group. DISTINCT ON doesn’t aggregate anything — it simply keeps the first row per group, based on whatever ORDER BY you specify, discarding the rest.
-- Get the highest single sale per region, with all its original columns intact
SELECT DISTINCT ON (region) region, product, amount, sale_date
FROM sales
ORDER BY region, amount DESC;
This is genuinely different from what GROUP BY can express cleanly. With GROUP BY, I could get MAX(amount) per region easily enough, but if I also wanted the product and sale_date associated with that specific highest sale, GROUP BY alone can’t give me that without an extra subquery or window function — every non-aggregated column would need to be in the GROUP BY, which would defeat the purpose. DISTINCT ON solves exactly this “give me the full row associated with the extreme value per group” problem in one clean, readable statement, and it’s a pattern unique to PostgreSQL that I genuinely miss when working with other database systems.
Common Use Cases
- Sales/revenue reporting broken down by category, region, or time period.
- User analytics: counting signups per day, active users per plan tier.
- Inventory summaries: total stock per warehouse, per category.
- Financial rollups: subtotals and grand totals for statements and reports.
- Data quality audits: counting duplicate or missing values grouped by key fields.
GROUP BY on Computed Columns From a Join
A pattern that comes up a lot in real reporting work: grouping by a column that comes from a joined lookup table rather than the main table being aggregated.
SELECT r.manager, SUM(s.amount) AS total_sales, COUNT(DISTINCT s.region) AS regions_covered
FROM sales s
JOIN regions r ON r.name = s.region
GROUP BY r.manager
ORDER BY total_sales DESC;
This groups sales data not by the raw region column, but by which manager is responsible for that region — a business-meaningful grouping that doesn’t exist directly in the sales table itself. I use this pattern constantly: the raw transactional table rarely has the exact grouping dimension a report needs, so joining in a lookup or dimension table first, then grouping by a column from that joined table, is a normal and expected part of building real reports rather than an edge case.
Why GROUP BY Sometimes Feels Slower Than Expected
If a GROUP BY query feels slower than you’d expect given the table size, it’s worth understanding what’s actually happening. PostgreSQL has to either sort the entire relevant dataset by the grouping columns, or build a hash table covering every distinct group, before it can start producing aggregated output. On a table with millions of rows and a grouping column with very high cardinality (many distinct values, like a raw email or timestamp column with second-level precision), this can mean touching almost every row in the table with real computational cost, not just a metadata lookup.
This is different from something like SELECT * FROM table WHERE id = 5, which can often resolve almost instantly via an index without touching most of the table at all. Aggregation is fundamentally different — it usually has to process every relevant row at least once, which is why indexing the columns used in WHERE (to reduce the input row count before aggregation even starts) tends to matter far more than indexing the GROUP BY columns themselves for large-scale reporting queries.
Troubleshooting Tips
Error: “column must appear in the GROUP BY clause.” Every non-aggregated column in your SELECT list needs to be in GROUP BY. Add it there, or wrap it in an aggregate function like MAX() or ARRAY_AGG() if you just need a representative value.
My groups are more fragmented than expected. Check whether you’ve added an extra column to GROUP BY that isn’t functionally dependent on your intended grouping — this silently splits groups you meant to combine.
HAVING isn’t filtering correctly. Make sure you’re not confusing it with WHERE. HAVING only works on aggregated values, evaluated after grouping — WHERE conditions belong on raw row-level filters, evaluated before grouping.
GROUP BY query is slow on a large table. Check EXPLAIN ANALYZE — PostgreSQL will use either a sort-based or hash-based aggregation strategy depending on data size and available memory. Indexing the columns used in WHERE and GROUP BY can help significantly, and increasing work_mem for the session can help PostgreSQL choose a faster hash aggregate over disk-based sorting for large groupings.
Best Practices I Follow
- Only include columns in
GROUP BYthat you actually need to distinguish groups by — extra columns fragment your results unexpectedly. - Use
HAVINGfor aggregate-based filtering,WHEREfor row-level filtering — never mix them up. - Use
ROLLUPwhen a report needs subtotals and a grand total, instead of manually combining multiple queries. - Group by expressions like
DATE_TRUNC()for clean time-based reporting instead of grouping by raw timestamps. - Index columns used in both
WHEREandGROUP BYfor large tables. - Double check functional dependencies before assuming an extra grouping column is “safe” to add.
Frequently Asked Questions
Can I use column aliases in GROUP BY? Yes, PostgreSQL allows referencing a SELECT list alias directly in GROUP BY, which is more permissive than some other database systems.
What’s the difference between GROUP BY and DISTINCT? DISTINCT removes duplicate rows from the output without performing any aggregation. GROUP BY collapses rows into groups specifically so you can apply aggregate functions to each group. If you just need unique values with no calculations, DISTINCT is simpler; GROUP BY is for when you need to compute something per group.
Can I GROUP BY a column not in the SELECT list? Yes — you can group by a column purely for grouping purposes without including it in the output, as long as every selected non-aggregate column still satisfies the GROUP BY rule.
Does GROUP BY automatically sort the results? No, GROUP BY doesn’t guarantee any particular output order. Always add an explicit ORDER BY if row order matters to you.
What’s the difference between ROLLUP and GROUPING SETS? ROLLUP automatically generates a specific hierarchical set of subtotals based on the column order you provide. GROUPING SETS gives you full manual control over exactly which combinations of groupings you want, without the fixed hierarchy ROLLUP assumes.
Wrapping Up
GROUP BY is the clause that turns raw transactional data into meaningful summaries — and once the mental model clicks (rows collapse into buckets, then aggregates run per bucket), the syntax errors that used to trip me up basically disappear. Start with simple single-column grouping, get comfortable with the HAVING vs WHERE distinction, and once you’re building real reports, ROLLUP and GROUPING SETS will save you from writing unnecessarily complicated multi-query workarounds. This is one of those SQL fundamentals that pays off in nearly every project you’ll ever touch.