Count and sum by status

Count orders by status: columns status, orders (the count) and revenue (the sum of total).

Statuses with the most orders first, and for equal counts by status name.

Tables

CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10, 2) NOT NULL
);

SELECT status
FROM orders;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO orders VALUES
  (1, 10, 'paid', 100.00),
  (2, 11, 'paid', 50.50),
  (3, 10, 'cancelled', 20.00),
  (4, 12, 'paid', 10.00),
  (5, 13, 'pending', 75.00);

-- equal counts: sorted by status
INSERT INTO orders VALUES
  (1, 1, 'shipped', 10.00),
  (2, 2, 'paid', 20.00),
  (3, 3, 'shipped', 30.00),
  (4, 4, 'paid', 40.00);

○
```
-- no orders at all
SELECT 1;
```