Lessons › SQL › Chapter 3: Summarising

SQL lesson

GROUP BY with multiple columns

GROUP BY with multiple columns splits results into finer slices by combining two or more grouping criteria at once, giving you a separate total for every unique combination.

Example

Breaking Down Retail Results with GROUP BY on Multiple Columns

SELECT category, region, SUM(revenue) AS total_revenue
FROM orders
GROUP BY category, region
ORDER BY category, region

Result:

Step by step

1. Run the query without SUM so you can see what the unfiltered values look like.

   SELECT category, region, revenue
   FROM orders
   GROUP BY category, region
   ORDER BY category, region;

2. SUM totals the numbers in a column. Works on numeric columns only.

   SELECT category, region, SUM(revenue)
   FROM orders
   GROUP BY category, region
   ORDER BY category, region;

3. AS gives the SUM result a friendly column name so the output reads cleanly.

   SELECT category, region, SUM(revenue) AS total_revenue
   FROM orders
   GROUP BY category, region
   ORDER BY category, region;

Practice

Try each one in your head first, then open the answer.

SELECT order_id, customer_id, COUNT(*) FROM orders GROUP BY order_id, customer_id

SELECT sale_id, store_id, COUNT(*) FROM store_sales GROUP BY sale_id, store_id

SELECT product_id, brand, COUNT(*) FROM products GROUP BY product_id, brand

SELECT review_id, product_id, COUNT(*) FROM reviews GROUP BY review_id, product_id

SELECT employee_id, full_name, COUNT(*) FROM employees GROUP BY employee_id, full_name