GROUP BY Basics

Easy6 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have a sales table.

Table: sales

id	region	product	sale_date	revenue
1	North	Laptop	2025-01-05	1200
2	North	Phone	2025-01-09	800
3	South	Laptop	2025-02-01	1000
4	South	Laptop	2025-02-14	1100
5	South	Phone	2025-03-02	600

Task

Explain what GROUP BY does.
Return the total revenue per region.
Return the total revenue per region and product, with a subtotal per region and a grand total.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

GROUP BY puts rows with the same values into one group, then aggregate functions run once per group.
Every selected column must be either in GROUP BY or wrapped in an aggregate.
You can group by several columns. A group is each unique combination.
ROLLUP adds subtotals and a grand total. CUBE adds every combination. GROUPING SETS lets you pick the combinations.

1Basic

One level at a time

1-- Total revenue per region2SELECT region, SUM(revenue) AS total_revenue3FROM sales4GROUP BY region;5 6-- Total revenue per region and product7SELECT region, product, SUM(revenue) AS total_revenue8FROM sales9GROUP BY region, product;

Result: North 2000, South 2700
subtotals and a grand total would need extra queries

2Optimized

ROLLUP in a single pass

1CREATE INDEX idx_sales_date_region_product ON sales (sale_date, region, product) INCLUDE (revenue);2 3SELECT region, product, SUM(revenue) AS total_revenue4FROM sales5WHERE sale_date >= DATE '2025-01-01'6GROUP BY ROLLUP (region, product)7ORDER BY region, product;

Output: detail rows, a subtotal per region (product is NULL) and a grand total (both NULL)
MySQL syntax: GROUP BY region, product WITH ROLLUP
Use GROUPING(col) to tell a subtotal NULL from a real NULL

Spotted a mistake in these answers?

Report a problem

Question

Problem

Hints

Related questions

Answers

2 ways to solve it

Quick answer