SQL Report Project: Check a Customer Summary from Start to Finish

This project combines querying and validation into one small report. You will create a customer summary, explain what each row means and prove that its counts and totals match the source. A correct-looking table is only the starting point.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Write the report definition

Each output row should represent one registered customer. Include the customer's name, order count, total amount and average order amount. Customers with no orders should remain visible. Sort by total amount descending and customer ID ascending as a tie-breaker.

Our original sample has no customer without orders, but using LEFT JOIN still expresses the intended report design. Test an unmatched case separately before applying the method to a larger dataset.

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_amount,
       AVG(o.amount) AS average_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC, c.customer_id;

The rows are Amina with 2, 1800 and 900.0; Chao with 1, 1500 and 1500.0; and Brian with 1, 800 and 800.0. The name is grouped with the customer key so the report remains explicit.

Explain the missing-value choices

COUNT(o.order_id) counts matched orders rather than the preserved customer row. COALESCE turns a no-order total into zero for this reporting rule. AVG remains NULL when no order amount exists, because an average order amount is undefined for a customer with no orders.

Do not automatically convert that average to zero. Zero could be mistaken for the average of actual zero-valued orders. A user-facing report can display Not applicable while preserving the distinction in its data.

Reconcile the totals independently

SELECT COUNT(*) AS source_orders,
       SUM(amount) AS source_total
FROM orders;

The source has 4 orders totalling 4100. Add the report's counts and totals: 2 + 1 + 1 = 4, and 1800 + 1500 + 800 = 4100. If the sums differ, inspect unmatched references, duplicate lookup keys or filters before changing the expected answer.

Test a deliberate variation

Add a customer through a CTE rather than changing the stored table, as in the LEFT JOIN lesson. The new customer should have count 0, total 0 and average NULL. This checks a requirement that the original happy-path data cannot test.

Also ask what a future date filter should mean. Keeping every customer while counting only orders in a month requires careful placement of the order-date rule. Putting a right-side filter in WHERE can remove the zero-order customers.

Document the result

State the data source, units, included period and missing-value rules beside a real report. Our sample has no dates, so it is an all-orders practice summary. Do not label it this month's sales without a date field and a defined filter.

Try it yourself

Using the report query as a base, add a HAVING rule to show only customers with total amount at least 1500. What changes in the reconciliation, and why should the filtered total no longer equal all source orders?

Keep exploring: SQL learning path.