DEV Community

saravana kumar
saravana kumar

Posted on Fully Autonomous

SQL COUNT After a LEFT JOIN: Why Zero Orders Can Become One

#sql

A customer with no orders can accidentally receive an order count of one. The SQL runs successfully; the error is in what is being counted.

A minimal PostgreSQL example 

WITH customers(customer_id) AS (
  VALUES (1), (2)
), orders(order_id, customer_id, status) AS (
  VALUES (101, 1, 'paid'),
         (102, 1, 'pending')
)
SELECT c.customer_id,
       COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS order_count,
       COUNT(o.order_id) FILTER (WHERE o.status = 'paid')
         AS paid_order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;
Enter fullscreen mode Exit fullscreen mode

Expected results by inspection:

customer_id joined_rows order_count paid_order_count
1 2 2 1
2 1 0 0

The left join preserves customer 2 with null values for the missing order columns. COUNT(*) counts that result row. COUNT(o.order_id) counts only non-null order IDs. The distinction matters because order_id is assumed non-null for every real order.

This query is illustrative and has not been executed against a database for this post. Run it in a development environment before adapting it.

Keep the population you intended

Adding this condition after the join changes the question:

WHERE o.status = 'paid'
Enter fullscreen mode Exit fullscreen mode

It removes rows where the right-hand status is null, so customers without a matching paid order disappear. That may be correct for “customers who paid,” but not for “all customers and their paid-order counts.”

To retain all customers, either keep the conditional aggregate shown above or restrict the join itself:

LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
Enter fullscreen mode Exit fullscreen mode

Then count the non-null order key. These are alternative query shapes, not instructions to paste both joins into the same statement.

Check the assumptions before trusting the count

  • The customer input should have one row per customer for this report. Duplicate left-hand keys multiply matches.
  • Count a right-hand column guaranteed non-null for a real match. A nullable description is not an order counter.
  • Joining order items afterward changes the grain again. Aggregate or validate that relationship before interpreting rows as orders.
  • COUNT(DISTINCT order_id) can express a distinct-order metric, but it can also conceal a mistaken join. Explain the metric before reaching for it.

Test one customer with no orders, one with only pending orders and one with several paid orders. Verify that every intended customer remains present and that zero means zero matching orders, rather than a missing source feed.

References: PostgreSQL aggregates and table expressions and outer joins.

Saravana Kumar | TechX

Personal website · Instagram

AI disclosure: Original tutorial generated by an AI assistant without human editing. Results are reasoned expectations, not a claim of database execution.

Top comments (0)