Grouping by Multiple Columns

Objectives

By the end of this lesson, you should be able to:

• Group rows by more than one column at once
• Explain how multi-column grouping changes what counts as “the same group”
• Read a multi-column GROUP BY result

💡 Why this matters: “Orders per region” is one level of summary. “Orders per region, broken down by product” is more useful, and more common in real reporting. Grouping by more than one column produces exactly that.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using a new orders table for this lesson.

Setup

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL,
    product VARCHAR(50) NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (customer_name, region, product, amount) VALUES
  ('Riley Nguyen', 'West', 'Widget', 120.00),
  ('Riley Nguyen', 'West', 'Gadget', 45.00),
  ('Casey Brooks', 'East', 'Widget', 120.00),
  ('Morgan Alvarez', 'West', 'Widget', 120.00),
  ('Drew Patel', 'East', 'Gizmo', 75.00),
  ('Jamie Kowalski', 'East', 'Widget', 120.00),
  ('Riley Nguyen', 'West', 'Widget', 120.00);

Grouping by Two Columns

SELECT region, product, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY region, product
ORDER BY region, product;

 region | product | order_count | total
--------+---------+--------------+--------
 East   | Gizmo   |            1 |  75.00
 East   | Widget  |            2 | 240.00
 West   | Gadget  |            1 |  45.00
 West   | Widget  |            3 | 360.00

GROUP BY region, product forms one group per unique combination of region and product, not one group per region and a separate one per product. East + Widget is one group (2 orders), West + Widget is a different group (3 orders), even though both share the same product. This is the same reasoning as multi-column ORDER BY (Module 6): each additional column narrows what counts as “the same” further.

Reading the Result

Each row of this output answers a specific, narrow question: “how many Widget orders came from the West region, and what did they total?” (3 orders, 360.00). A single-column GROUP BY region alone would answer a broader question, total orders per region regardless of product, losing the product breakdown entirely. The number of columns in GROUP BY controls how fine-grained the summary is.

Try It

1. Write a query showing the number of orders and total amount, grouped by region alone (not product).
2. Write a query showing the number of orders, grouped by customer_name and product together.
3. Compare the result of question 1 to the multi-column result in this lesson, explain in your own words what information is lost by dropping product from the grouping.
4. Write a query showing region, product, and average order amount, grouped by both columns, keeping only groups with more than 1 order (hint: this needs HAVING from the previous lesson too).

Recap

• GROUP BY col1, col2 groups by the combination of both columns, one group per unique pairing of values.
• Adding more columns to GROUP BY produces a more detailed, more fine-grained summary, fewer rows per group, more groups overall.
• HAVING still works the same way with multi-column grouping, filtering based on each group’s aggregate value.

Next lesson: DISTINCT combined with aggregate functions, counting only unique values within a group.