0Pricing
SQL Interview Prep · Lesson

Grouping by Multiple Columns and Expressions

Composite grouping keys and grouping on computed values.

Grouping by Multiple Columns and Expressions is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Beyond a Single Grouping Key

Real reports rarely group by one column. Interviewers escalate from 'sales per region' to 'sales per region per month' to see if you understand composite grouping keys.

The rule scales cleanly: listing more columns in GROUP BY creates one row per distinct combination of those columns.

What Multiple Columns Mean

When you write GROUP BY region, product, the group key is the pair (region, product). Each unique pair becomes one output row.

  • 5 regions and 4 products yield up to 20 groups.
  • Combinations that never occur produce no row at all.
  • Order of columns in GROUP BY does not change the result set, only sometimes the plan.
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product;

Still Obey the SELECT Rule

The core constraint does not relax. Every non-aggregated SELECT column must appear in GROUP BY. With composite keys you simply list them all.

Forgetting one column in a multi-key group is the most common slip under interview pressure. Match the GROUP BY list to the non-aggregated SELECT columns exactly.

-- Both region and product appear in GROUP BY
SELECT region, product,
       COUNT(*) AS orders,
       AVG(amount) AS avg_amount
FROM sales
GROUP BY region, product;

Grouping by an Expression

You can group by a computed value, not just a raw column. A frequent ask is grouping sales by month, which means grouping by a truncated or extracted date.

The expression in GROUP BY must match the expression in SELECT. The engine groups by the computed result.

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS monthly_total
FROM sales
GROUP BY DATE_TRUNC('month', order_date);

Grouping With CASE Buckets

A powerful pattern: group by a CASE expression to create custom buckets. This is how you produce 'small / medium / large order' summaries without a lookup table.

Interviewers like this because it tests both CASE logic and grouping on a derived value at once.

SELECT CASE
         WHEN amount < 50  THEN 'small'
         WHEN amount < 200 THEN 'medium'
         ELSE 'large'
       END AS bucket,
       COUNT(*) AS orders
FROM sales
GROUP BY CASE
         WHEN amount < 50  THEN 'small'
         WHEN amount < 200 THEN 'medium'
         ELSE 'large'
       END;

Grouping by Column Position

Many dialects let you group by ordinal position: GROUP BY 1, 2 means the first and second SELECT columns. It is concise but fragile.

Interviewers may accept it, but note the risk: reordering the SELECT list silently changes the grouping. Prefer explicit expressions in production code.

-- 1 = month expression, 2 = region
SELECT DATE_TRUNC('month', order_date) AS month,
       region, SUM(amount)
FROM sales
GROUP BY 1, 2;

NULLs Form Their Own Group

A NULL handling trap inside grouping: when a grouping column contains NULLs, all NULL rows collapse into a single group.

This differs from equality comparisons where NULL never equals NULL. For grouping purposes, NULLs are treated as 'the same' and form one bucket. Interviewers love the apparent contradiction.

-- Rows with region IS NULL all land in one group
SELECT region, COUNT(*) AS orders
FROM sales
GROUP BY region;

GROUPING SETS for Multiple Levels

To produce several grouping granularities in one query, use GROUPING SETS. It computes each listed set of keys and unions the results.

This is a mid-to-senior signal: instead of three separate queries plus UNION, one statement returns per-region, per-product, and grand totals together.

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region), (product), ());

ROLLUP and CUBE

ROLLUP and CUBE are shorthand for common grouping sets. ROLLUP(region, product) gives region+product, region subtotals, and a grand total, ideal for hierarchical reports.

CUBE generates every combination of the listed columns. Knowing these distinguishes a candidate who has built real reporting queries.

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);

Worked Example: Monthly Region Report

Combine the ideas: total and average sales per region per month, only for completed orders, sorted for readability.

WHERE narrows rows first, the composite key groups by region and month expression, and ORDER BY arranges the output. This is a typical full analyst-interview answer.

SELECT region,
       DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS total,
       AVG(amount) AS avg_order
FROM sales
WHERE status = 'completed'
GROUP BY region, DATE_TRUNC('month', order_date)
ORDER BY region, month;

How to Reason Out Loud

For any composite-grouping question, state the key explicitly: 'I am grouping by the combination of X and Y, so I get one row per distinct (X, Y) pair.'

Then confirm each non-aggregated SELECT column is in that key, and mention NULLs collapse into one group. That methodical narration is what mid-level interviews reward.

Quick Check

Reason about composite grouping keys.

Recap

Composite keys: listing multiple columns produces one row per distinct combination; every non-aggregated SELECT column must be in the key.

  • You can group by expressions, CASE buckets, or ordinal positions (positions are fragile).
  • NULLs in a grouping column collapse into a single group.
  • GROUPING SETS, ROLLUP, and CUBE produce multiple grouping levels in one query.
  • Push row filters into WHERE before grouping.

Frequently asked questions

Is the “Grouping by Multiple Columns and Expressions” lesson free?

Yes — the full text of “Grouping by Multiple Columns and Expressions” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Grouping by Multiple Columns and Expressions”?

Composite grouping keys and grouping on computed values. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Grouping by Multiple Columns and Expressions” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. The GROUP BY Rule for SELECT Columns
  2. HAVING vs WHERE
  3. Grouping by Multiple Columns and Expressions
  4. Counting and Filtering Groups
← Back to SQL Interview Prep