Why LEFT JOIN and COUNT(*) can report one order for a new customer

Decide what one output row represents

A customer summary should contain one row per customer. Start from customers and LEFT JOIN orders so a new customer remains visible. If no order matches, the database produces one result row whose order columns are NULL. That is useful for keeping the customer, but it changes what COUNT(*) means.

Count a non-null order identifier

COUNT(*) counts every joined row, including that unmatched row. COUNT(o.id) counts only rows where the order identifier is not NULL. When the identifier is a non-null primary key, that gives the number of real orders. COALESCE supplies zero when SUM has no non-null values.

SELECT c.id, c.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.amount), 0) AS net_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

A filter can accidentally remove the customer

Placing WHERE o.amount > 0 after the join removes the unmatched row and can also remove customers whose orders are all refunds. Decide whether the report means all orders, only positive orders, or customers meeting a condition. To count only qualifying orders while retaining all customers, put the qualification in the join condition or use conditional aggregation.

Add a test before adding another join

Use a new customer, one with two orders, one with a refund and an order whose customer_id is NULL. Then consider joining order lines: each order may appear several times and its amount may be counted repeatedly. Preaggregate each collection to the intended row grain. Run the query-plan lab to inspect access paths, and benchmark representative data before making performance claims.