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

Order Value Metric Snapshot

Return one company-wide order count and value summary.

  • Aggregation
  • Numeric functions

Exercise brief

Understand the request

Revenue operations analyst Operations needs one order-value scorecard that reconciles row volume with populated measures.

Return one company-wide order-value scorecard that distinguishes rows, populated measures, NULL, and zero.

Return

  • Return total_orders, valued_orders, total_order_value, average_order_value, min_order_value, max_order_value in this exact left-to-right order.

Constraints

  • COUNT(*) includes all orders; value aggregates ignore NULL order_amount but retain zero.
  • Round average_order_value to 2 decimal places.

Data you will use

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

orders

  • order_idINTEGER
  • order_amountDECIMAL(12,2)

Hints, when you need them

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

Hint 1

Keep the report at company-wide grain: aggregate the orders table without GROUP BY.

Hint 2

COUNT(*) counts rows; COUNT(order_amount) and the value aggregates ignore a NULL amount but still include zero.

Hint 3

SELECT COUNT(*) AS total_orders, COUNT(/* nullable measure */) AS valued_orders, SUM(/* measure */) AS total_order_value, ROUND(AVG(/* measure */), 2) AS average_order_value, MIN(/* measure */) AS min_order_value, MAX(/* measure */) AS max_order_value 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, COUNT(order_amount) AS valued_orders, SUM(order_amount) AS total_order_value, ROUND(AVG(order_amount), 2) AS average_order_value, MIN(order_amount) AS min_order_value, MAX(order_amount) AS max_order_value FROM orders;

Why this works

Correctness: COUNT(*) measures order rows, while COUNT(order_amount) and the value aggregates ignore only NULL amounts; zero remains a real value and therefore the minimum. Edge case: on empty input both counts return 0, while SUM, AVG, MIN, and MAX return NULL unless the reporting contract explicitly applies a default. Portability: these standard aggregates are supported across the live engines, though exact numeric result types can differ.

Success check

Exactly one row reconciles 12 orders and 11 valued orders.

Expected result

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

total_ordersvalued_orderstotal_order_valueaverage_order_valuemin_order_valuemax_order_value
12111180107.270240

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.