Skip to content
SQLSimplified
aggregate

COUNT(): Row Tallies and Distinct Counts

Compare COUNT(*), COUNT(column), and COUNT(DISTINCT column) on the customers table.

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

Loading playground environment...

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.

When DISTINCT matters

COUNT(DISTINCT city) answers "how many cities do we operate in?", impossible with a plain COUNT(*).

Variations

Count distinct product categories ordered:

Loading playground environment...

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.

Related examples

Core Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
aggregate
A single query demonstrating all five core aggregates over the employees table.
COUNT() Aggregation With GROUP BY
group by
Count how many movies each director has made using COUNT() grouped by director.

Cite this resource

SQLSimplified. "COUNT(): Row Tallies and Distinct Counts Example". Available at: https://sqlsimplified.online/examples/count-usage