Revenue by Region and Product

Mediumsql

Learn the Concept: Multiple Grouping Columns

Write a query returning region, product, and total (sum of amount) for each region+product combination, ordered by region then product.

sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  product TEXT NOT NULL,
  amount INTEGER NOT NULL
);

Sample data:

sql
INSERT INTO orders (id, region, product, amount) VALUES
(1, 'North', 'Widget', 200),
(2, 'North', 'Gadget', 150),
(3, 'South', 'Widget', 100),
(4, 'South', 'Gadget', 50),
(5, 'North', 'Widget', 300),
(6, 'East', 'Widget', 400);

Example 1

Input

(none)

Output

region	product	total
East	Widget	400
North	Gadget	150
North	Widget	500
South	Gadget	50
South	Widget	100

Grouping by two columns produces one row per unique combination. Reference: SELECT region, product, SUM(amount) AS total FROM orders GROUP BY region, product ORDER BY region, product;

Revenue by Region and Product

Related Problems

Input (stdin)

Output