Widget vs Gadget Revenue by Region

Hardsql

Learn the Concept: Conditional Aggregation

Write a query returning region, widget_total (sum of amount where product is 'Widget', else 0), and gadget_total (sum of amount where product is 'Gadget', else 0) — one row per region, ordered by region.

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	widget_total	gadget_total
East	400	0
North	500	150
South	100	50

A CASE expression inside SUM computes a conditional total without needing separate queries. Reference: SELECT region, SUM(CASE WHEN product = 'Widget' THEN amount ELSE 0 END) AS widget_total, SUM(CASE WHEN product = 'Gadget' THEN amount ELSE 0 END) AS gadget_total FROM orders GROUP BY region ORDER BY region;

Widget vs Gadget Revenue by Region

Related Problems

Input (stdin)

Output