Skip to content

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_orders includes every row.
  • orders_with_coupon skips orders where coupon_code is NULL.
  • distinct_coupons tells you how many different codes were used.
  • ordering_customers is 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)).