Ravindra BagaleCourses & study guides Track your progress

11. Aggregate Functions: SUM COUNT AVG MIN MAX

11.3 COUNT(*) vs COUNT(DISTINCT column)

By Last updated:

Why. Counting orders vs counting unique customers are different questions.

What. COUNT(*) = rows. COUNT(DISTINCT col) = unique values in that column.

How. SELECT COUNT(*) , COUNT(DISTINCT customer_id) FROM orders;

Steps

  1. Count orders.
  2. Count distinct customers who ordered.
  3. Explain the gap.

Examples

Example 1 · dataset: raja_coffee

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS unique_customers,
       COUNT(DISTINCT city) AS unique_cities
FROM orders;

Output

ordersunique_customersunique_cities
2486

Meaning. Orders vs people vs cities.

Ravindra Bagale's Tip

COUNT(DISTINCT …) is slower on huge tables — fine on practice sizes.