Aggregate Rows with GROUP BY

You've built tables, written queries, and filtered data—but the moment your analysis demands answers like "How many orders did each customer place?" or "What's the average salary per department?", raw rows don't give you the story. That's where PostgreSQL's GROUP BY clause steps in to transform scattered records into meaningful summaries. In this lesson, you'll not only learn the syntax but also develop an intuition for when and how to aggregate rows with GROUP BY, turning messy data into valuable insights.

The problem this lesson solves

Handling thousands of raw transaction rows is overwhelming and unhelpful for decision-making. Without GROUP BY, you'd have to manually count or compute totals outside the database, leading to errors, inefficiency, and stale answers. For example, calculating the total sales per region from a million-row orders table in Python would require transferring all that data—a disaster for performance and memory.

Pain point: Without aggregation, your queries return too much detail, making it impossible to see patterns or draw conclusions quickly.

PostgreSQL's GROUP BY lets the database engine do the heavy lifting: collapsing rows that share a common value (like customer_id or region) and applying an aggregate function—COUNT, SUM, AVG, MIN, MAX—to each group. The result is a compact, insightful summary without sacrificing performance.

Core concept / mental model

Think of GROUP BY as a sorting hat for your data. It examines every row in the result set (after any WHERE filtering) and sorts them into buckets based on one or more columns. Then, one aggregate function runs against each bucket, producing a single output row per bucket. The output row contains the grouping column(s) and the calculated aggregate value.

A diagram in words

Raw orders table (sample):

After SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;:

Each GROUP BY bucket aggregates rows with the same customer_id. The COUNT(*) counts rows per bucket—output one row per group.

Key definitions

• Grouping column(s): Columns listed in the GROUP BY clause—they define the buckets.
• Aggregate function: Operates on a group of rows to return a single value: COUNT, SUM, AVG, MIN, MAX, array_agg, string_agg, etc.
• Result set: After grouping, each row corresponds to one unique combination of grouping column values.

How it works step by step

Follow these logical steps the PostgreSQL engine takes (conceptually) when processing a GROUP BY query:

1. Filter rows (WHERE) — The database first applies any WHERE conditions to exclude rows before grouping. This is crucial: WHERE runs before aggregation, so you cannot use aggregate functions in WHERE (use HAVING instead).
2. Group remaining rows — Rows are split into groups based on the GROUP BY columns. A group exists for each distinct combination of those column values.
3. Aggregate each group — For each group, the engine evaluates the aggregate functions (e.g., SUM(amount), COUNT(*)) across all rows in that group.
4. Project output columns — The SELECT list can include grouping columns, aggregate expressions, or constant expressions—but cannot include non-grouped, non-aggregated columns (PostgreSQL will raise an error).
5. Filter groups (HAVING) — If present, HAVING removes whole groups based on a condition that often references aggregate results (e.g., HAVING COUNT(*) > 10).
6. Order results (ORDER BY) — Finally, rows are sorted (if requested). You can order by grouping columns or aggregate expressions (aliases allowed).

Execution order reminder

The logical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This explains why WHERE can't use aliases from SELECT—the alias isn't defined until later.

Hands-on walkthrough

Let's create a sample table and practice aggregating rows with GROUP BY. We'll use a simple sales table for a retail store.

-- Create sample sales table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product TEXT NOT NULL,
    region TEXT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    sale_date DATE NOT NULL
);

-- Insert sample data
INSERT INTO sales (product, region, amount, sale_date) VALUES
('Laptop', 'North', 1200.00, '2024-01-15'),
('Laptop', 'South', 1100.00, '2024-01-16'),
('Mouse',   'North', 25.00,  '2024-01-15'),
('Mouse',   'South', 30.00,  '2024-01-16'),
('Laptop', 'North', 1250.00, '2024-02-10'),
('Keyboard','South', 70.00,  '2024-02-12'),
('Mouse',   'North', 28.00,  '2024-02-11');

Example 1: Count sales per product

SELECT product, COUNT(*) AS total_sales
FROM sales
GROUP BY product;

Output:

  product  | total_sales
-----------+-------------
 Laptop    |           3
 Mouse     |           3
 Keyboard  |           1

Example 2: Total sales amount per region

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

Output:

 region | total_revenue
--------+---------------
 North  |       2503.00
 South  |       1200.00

Example 3: Group by multiple columns

Group by both product and region to see breakdowns:

SELECT product, region, COUNT(*) AS cnt, AVG(amount) AS avg_amount
FROM sales
GROUP BY product, region
ORDER BY product, region;

Output:

  product  | region | cnt |     avg_amount
-----------+--------+-----+-------------------
 Keyboard  | South  |   1 |             70.00
 Laptop    | North  |   2 |           1225.00
 Laptop    | South  |   1 |           1100.00
 Mouse     | North  |   2 |             26.50
 Mouse     | South  |   1 |             30.00

Example 4: Filter groups with HAVING

Show only products that appear in more than 1 sale:

SELECT product, COUNT(*) AS cnt
FROM sales
GROUP BY product
HAVING COUNT(*) > 1;

Output:

 product | cnt
---------+-----
 Laptop  |   3
 Mouse   |   3

Pro tip: Use HAVING to filter groups based on aggregate values; use WHERE to filter rows before grouping.

Compare options / when to choose what

When you need aggregated data, you have several SQL constructs. This table compares them:

Variations

• GROUPING SETS, ROLLUP, CUBE — PostgreSQL supports advanced grouping for multiple subtotal levels in one query (e.g., ROLLUP (region, product) gives totals per region and grand total).
• FILTER clause on aggregates — You can conditionally include rows in a specific aggregate: COUNT(*) FILTER (WHERE amount > 100).
• array_agg or string_agg — Instead of numeric aggregates, you can collect values into arrays or strings per group.

Troubleshooting & edge cases

Aggregating rows with GROUP BY is powerful, but it comes with common pitfalls:

1. Selecting a non-grouped column

-- ERROR: column "sales.product" must appear in the GROUP BY clause or be used in an aggregate function
SELECT product, region, COUNT(*) FROM sales GROUP BY product;

Fix: Include region in GROUP BY or apply an aggregate like MAX(region).

2. Using WHERE with aggregate functions

-- ERROR: aggregate functions are not allowed in WHERE
SELECT product, COUNT(*) FROM sales WHERE COUNT(*) > 1 GROUP BY product;

Fix: Use HAVING COUNT(*) > 1.

3. NULL values in grouping column

Rows with NULL in the grouping column are grouped together (they form their own group). For example, if region is NULL, those rows sit in one bucket.

4. Misunderstanding COUNT(*) vs COUNT(column)

• COUNT(*) counts all rows, including those with NULLs.
• COUNT(region) counts only non-null values in region.

5. Ordering by aggregate alias

PostgreSQL allows ORDER BY on aliases, but be careful when using ORDER BY on non-aggregated columns—it will fail if not in GROUP BY.

6. Performance with large tables

GROUP BY often requires sorting or hashing. Use indexes on grouping columns to speed up. Check query plan with EXPLAIN.

What you learned & what's next

You've unlocked the power to aggregate rows with GROUP BY in PostgreSQL. Now you can:

• Explain the core concept: grouping rows into buckets and applying aggregate functions.
• Write queries using GROUP BY with single and multiple columns.
• Filter groups using HAVING and differentiate it from WHERE.
• Handle common errors and edge cases like non-grouped columns and NULLs.

Next step: In the next lesson, you'll dive into window functions, which let you compute aggregations without collapsing rows—giving you the best of both worlds. You'll use OVER (PARTITION BY ...) to see running totals, rankings, and moving averages. Get ready to take your analytics to the next level.