High-Revenue Regions Only

Mediumsql

Write a query returning region and total (sum of amount) only for regions whose total revenue exceeds 500, 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	total
North	650

HAVING filters groups by an aggregate condition, after GROUP BY has run. Reference: SELECT region, SUM(amount) AS total FROM orders GROUP BY region HAVING SUM(amount) > 500 ORDER BY region;

High-Revenue Regions Only

Related Problems

Input (stdin)

Output