Aggregation & GROUP BY · Easy
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
Short answer
COUNT(*) counts rows, whatever they contain. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. Choosing the wrong one is a frequent source of quietly wrong metrics, especially after LEFT JOINs, where padded rows contain NULLs but still count towards COUNT(*).
Detailed answer
SELECT COUNT(*) AS all_orders,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT coupon_code) AS distinct_coupons,
COUNT(delivered_date) AS orders_with_delivery_date,
COUNT(DISTINCT customer_id) AS ordering_customers
FROM orders;
Reading the output:
all_ordersincludes every row.orders_with_couponskips orders wherecoupon_codeis NULL.distinct_couponstells you how many different codes were used.ordering_customersis the number of unique buyers, not the number of orders.
The same rule applies to SUM and AVG: they ignore NULLs. AVG(x) divides by the count of non-NULL values, which can differ from SUM(x) / COUNT(*). If NULL should mean zero, say so explicitly with AVG(COALESCE(x, 0)).