SQL Aggregations SQL practice problemEasyVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Order Value Metric Snapshot

Return one company-wide order count and value summary.

Aggregation
Numeric functions

Interview 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.

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_orders	valued_orders	total_order_value	average_order_value	min_order_value	max_order_value
12	11	1180	107.27	0	240

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: