Define the grain of every table
Write what one row represents: a customer, an order or an order item. A join changes the available rows; aggregation then operates on those rows. PostgreSQL documents these as separate operations.
Before executing a query, sketch which records should match. A syntactically valid join can still answer the wrong business question.
Worked example: how 150 becomes 250
Suppose O1 has an order total of 100 and two item rows. O2 has a total of 50 and one item row. Joining orders to items gives O1, O1, O2, so SUM(order_total) is 250 instead of the correct order total of 150.
The order amount has been repeated at item grain. Aggregate the necessary item information to one row per order before joining, or calculate order totals directly from orders when items are unnecessary.
Grouping does not remove the underlying mistake
GROUP BY customer_id partitions the joined rows; it does not make repeated order totals unique. SUM(DISTINCT order_total) is also unsafe because different orders may legitimately have equal amounts.
For item-level sales, define how quantity and unit price produce a line amount. State the measure and its grain before choosing an aggregation.
Self-check: a customer with no orders
Add customer C3 with no orders. A customers LEFT JOIN orders retains a null-extended row. COUNT(*) for that customer is 1, whereas COUNT(order_id) is 0 if order identifiers are non-null.
Hand-calculate customers with two, one and zero orders. Check row counts before totals, then explain that WHERE filters input rows and HAVING filters groups. Use fictional practice data and produce assessed work independently.
References
Your next step
For a module-support enquiry, describe the module, learning goal and what you have tried. Appropriate teaching availability requires confirmation; assessed work remains your own.