COUNT(): Row Tallies and Distinct Counts

Overview

COUNT comes in three useful flavors: COUNT(*) counts rows, COUNT(col) counts non-null values of a column, and COUNT(DISTINCT col) counts unique values. This example contrasts them using the store's customer cities.

The Query

Step-by-Step Breakdown

1. COUNT(*) — every customer row (15).
2. COUNT(city) — non-null city values (all 15 are filled here).
3. COUNT(DISTINCT city) — unique cities; several customers share a city, so this is smaller than the total.

Variations

Count distinct product categories ordered:

Common Mistakes

• Assuming COUNT(col) equals COUNT(*). They differ whenever col can be NULL.
• Overusing DISTINCT. COUNT(DISTINCT id) is redundant since id is already unique — it just adds cost.
• DISTINCT with multiple columns. COUNT(DISTINCT a, b) counts distinct combinations in some engines; verify support.