SQL Aggregations SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Order Status Scorecard

Return one scorecard row with total and per-status order counts.

  • Aggregation
  • CASE expressions
  • NULL handling

Exercise brief

Understand the request

Order operations manager The daily control report needs status counts that reconcile to order volume.

Return one scorecard row containing total order volume and one count for each lifecycle status.

Return

  • Return total_orders, paid_orders, pending_orders, cancelled_orders, refunded_orders in this exact left-to-right order.

Constraints

  • Use CASE-based conditional aggregation in one scan of orders.
  • Statuses are mutually exclusive; return zero rather than NULL.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • statusVARCHAR(30)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Keep the report at one-row scorecard grain and evaluate each status condition over the same order population.

Hint 2

Use one CASE expression inside each aggregate; ELSE 0 keeps non-matching rows from contributing, and COALESCE handles empty input.

Hint 3

SELECT COUNT(*) AS total_orders, COALESCE(SUM(CASE WHEN /* paid */ THEN 1 ELSE 0 END), 0) AS paid_orders, COALESCE(SUM(CASE WHEN /* pending */ THEN 1 ELSE 0 END), 0) AS pending_orders, COALESCE(SUM(CASE WHEN /* cancelled */ THEN 1 ELSE 0 END), 0) AS cancelled_orders, COALESCE(SUM(CASE WHEN /* refunded */ THEN 1 ELSE 0 END), 0) AS refunded_orders FROM orders;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT COUNT(*) AS total_orders, COALESCE(SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END), 0) AS paid_orders, COALESCE(SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END), 0) AS pending_orders, COALESCE(SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END), 0) AS cancelled_orders, COALESCE(SUM(CASE WHEN status = 'REFUNDED' THEN 1 ELSE 0 END), 0) AS refunded_orders FROM orders;

Why this works

Correctness: each CASE expression evaluates the same order population and the mutually exclusive lifecycle values reconcile to COUNT(*). Edge case: COALESCE converts SUM's empty-input NULL to zero without changing real zero counts. Portability: CASE inside SUM and COALESCE are portable alternatives to dialect-specific filtered aggregate or pivot syntax.

Success check

One row returns correct status counts that sum to total_orders.

Expected result

Use this output to verify values, aliases, ordering, and row count.

total_orderspaid_orderspending_orderscancelled_ordersrefunded_orders
129111

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.