Part 52: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

The differences between count variations are a frequent source of calculation bugs.

1. The Differences

• COUNT(*): Counts every row in the table, including rows where columns are NULL.
• COUNT(column): Counts only rows where the specified column is NOT NULL.
• COUNT(DISTINCT column): Counts the number of unique non-null values.

-- Count total users vs count users with verified emails:
SELECT 
  COUNT(*) AS total_users,
  COUNT(verified_at) AS verified_users,
  COUNT(DISTINCT country) AS unique_countries
FROM users;

Key Takeaways

• Use COUNT(*) for total row counts.
• Use COUNT(col) when counting non-null values.

In Part 53, we will master Grouping Data with the GROUP BY Clause!