GROUP BY Multiple Columns in SQL with Examples

Grouping by a single column, such as region, answers a useful but sometimes too broad question: total sales per region. But many real business questions need a finer level of detail, such as total sales per region, broken down further by month. This is exactly what grouping by multiple columns accomplishes: instead of one bucket per region, you get one bucket per unique combination of region and month, revealing much richer, more actionable detail within the same query.

Key Definitions

• Multi-column GROUP BY: A GROUP BY clause listing two or more columns, creating one group per unique combination of values across all listed columns.
• Granularity: The level of detail in a grouped report; more grouping columns generally produce finer granularity (more, smaller groups).
• Composite group: A group defined by a combination of values across multiple columns, rather than a single column's value alone.

What You'll Learn

• Write a GROUP BY clause using two or more columns.
• Understand that grouping by multiple columns creates one group per unique combination of those columns.
• Recognize how adding more grouping columns increases the granularity (and number of rows) in the result.
• Apply multi-column GROUP BY to realistic reporting scenarios like sales by region and month.

Detailed Explanation

When GROUP BY lists a single column, such as GROUP BY region, every row sharing that same region value is collapsed into one group, regardless of any other differences between those rows. When GROUP BY lists multiple columns, such as GROUP BY region, month, MySQL instead creates a separate group for every unique combination of region and month values found in the data. A row with region='North', month='January' belongs to a different group than a row with region='North', month='February', even though both share the same region.

This has a direct, predictable effect on the result set: the more columns you add to GROUP BY, the more groups (and therefore more output rows) you will typically get, since each additional column can further subdivide the existing groups based on its own distinct values. Grouping by region alone might produce 4 rows (one per region), while grouping by region and month together might produce 24 rows (4 regions times 6 months of data, assuming every combination has at least one matching row).

The same core rule from single-column GROUP BY still applies: every column in the SELECT list must be either one of the grouping columns or wrapped in an aggregate function. With multiple grouping columns, this simply means both region and month (in our example) are safe to select directly, while any other raw column would need to be aggregated.

This technique is essential for building genuinely useful, drill-down-capable reports. A sales manager reviewing 'total revenue per region' might immediately follow up with 'but how did that revenue trend over the months within each region,' and grouping by both region and month in a single query answers exactly that follow-up question without needing a separate query.

Visual Summary

Draw two side-by-side result tables. Left table labeled 'GROUP BY region' showing 3 rows: North, South, West, each with one total. Right table labeled 'GROUP BY region, month' showing 9 rows (3 regions x 3 months), each combination getting its own row and total, captioned 'Adding a second grouping column increases granularity — more, smaller groups.'

Quick Reference

region	month	total_revenue
North	January	4500
North	February	5200
South	January	3100
South	February	2900
West	January	6000
West	February	5700

SQL Example

CREATE TABLE orders (
  order_id      INT PRIMARY KEY AUTO_INCREMENT,
  region        VARCHAR(50),
  order_month   VARCHAR(20),
  order_amount  DECIMAL(10,2)
);

INSERT INTO orders (region, order_month, order_amount) VALUES
  ('North', 'January', 2000), ('North', 'January', 2500),
  ('North', 'February', 5200),
  ('South', 'January', 3100),
  ('South', 'February', 2900),
  ('West',  'January', 6000),
  ('West',  'February', 5700);

-- Grouping by a single column: total revenue per region
SELECT region, SUM(order_amount) AS total_revenue
FROM orders
GROUP BY region;

-- Grouping by multiple columns: total revenue per region AND month
SELECT
  region,
  order_month,
  SUM(order_amount) AS total_revenue
FROM orders
GROUP BY region, order_month
ORDER BY region, order_month;

The first query collapses all orders into just three rows, one total per region, losing any month-level detail. The second query groups by both region and order_month together, producing one row for every unique region-month combination present in the data, revealing exactly how each region's revenue is distributed across different months — a much more detailed and actionable report than the single-column version.

Real-World Examples

• Retail chains group sales by store_location and product_category together to see which product categories perform best at each specific store.
• SaaS companies group revenue by subscription_plan and signup_month to analyze cohort-based revenue trends over time.
• University systems group enrollment counts by department and academic_year to track how program popularity shifts year over year within each department.
• Logistics companies group delivery counts by city and delivery_partner to evaluate individual partner performance within each specific city market.
• Marketing teams group campaign clicks by channel and campaign_name to compare how each campaign performed across different marketing channels.

Common Mistakes to Avoid

• Grouping by only one column when the actual business question requires a finer breakdown across two or more dimensions.
• Selecting a raw, non-grouping column in a multi-column GROUP BY query, expecting a single defined value per group.
• Not anticipating that adding more grouping columns significantly increases the number of output rows, which can affect report readability.
• Confusing the order of columns listed in GROUP BY with any implied sorting; GROUP BY does not guarantee row order without an explicit ORDER BY.

Interview Questions

Q1. What happens when you GROUP BY two columns instead of one?

MySQL creates a separate group for every unique combination of values across both columns, rather than one group per value of a single column, resulting in finer-grained groups and typically more rows in the output.

Q2. How would you build a report showing total sales per region and per month?

Group the sales data by both the region and month columns together, using GROUP BY region, month, and apply SUM on the sales amount column to get one total per unique region-month combination.

Q3. Does adding more columns to GROUP BY always increase the number of output rows?

Generally yes, since each additional grouping column can further subdivide existing groups based on its own distinct values, though the exact increase depends on how many unique combinations actually exist in the underlying data.

Q4. In a query grouped by region and month, is it safe to also select a raw column like order_id directly?

No, unless order_id happens to also be a grouping column, since a raw column like order_id could have multiple different values within the same region-month group, making it unsafe to select directly without wrapping it in an aggregate function.

Practice MCQs

1. GROUP BY region, month creates a separate group for:

1. Each distinct region only
2. Each distinct month only
3. Each unique combination of region and month
4. Every single row individually

Answer: C. Each unique combination of region and month

Explanation: Multi-column GROUP BY creates one group per unique combination of all listed columns, not per single column value alone.

2. Compared to grouping by region alone, grouping by region and month typically produces:

1. Fewer output rows
2. The same number of output rows
3. More output rows, assuming multiple months of data exist
4. Always exactly one row

Answer: C. More output rows, assuming multiple months of data exist

Explanation: Adding a second grouping column subdivides the original region-based groups further by month, generally increasing the total number of output rows.

3. In a query with GROUP BY region, month, which of these is safe to include in SELECT without an aggregate function?

1. order_id
2. order_amount
3. region and month
4. customer_name

Answer: C. region and month

Explanation: Only the grouping columns themselves (region and month) have a single, well-defined value per group; any other raw column would need to be wrapped in an aggregate function.

4. A report needing 'total revenue per region, broken down further by product category' requires:

1. GROUP BY region only
2. GROUP BY product_category only
3. GROUP BY region, product_category
4. No GROUP BY at all

Answer: C. GROUP BY region, product_category

Explanation: This report needs one total per unique combination of region and product category, which requires grouping by both columns together.

Quick Revision Points

• GROUP BY with multiple columns creates one group per unique combination of all listed column values.
• More grouping columns generally means finer granularity and more output rows.
• The SELECT list must still only include grouping columns or aggregate function results, extended to all listed grouping columns.
• Multi-column GROUP BY is essential for drill-down style reports, such as sales per region per month.

Conclusion

• Grouping by multiple columns unlocks finer-grained, more actionable reporting than a single grouping column allows.
• Each additional grouping column subdivides existing groups, generally increasing the number of resulting rows.
• The same safe-SELECT rule from single-column GROUP BY extends naturally to all listed grouping columns.
• This technique is foundational for building genuinely useful drill-down and cross-tabulated business reports.

Grouping by multiple columns in SQL creates one group for every unique combination of values across all the listed grouping columns, rather than one group per single column value. This enables finer-grained, more actionable reports, such as total revenue broken down by both region and month simultaneously, instead of just by region alone. As with single-column GROUP BY, every selected column must either be one of the grouping columns or wrapped in an aggregate function, and adding more grouping columns generally increases the granularity and row count of the final report.