COUNT Function

Overview

The COUNT aggregate function counts the number of occurrences or rows within a table or partition.

Learning Objectives

• Distinguish between COUNT(*), COUNT(column), and COUNT(1).
• Use COUNT(DISTINCT col) to calculate unique occurrences.
• Group counts by attributes.

Detailed Concept Explanation

• COUNT(*): Counts all rows matching the query bounds, including rows with NULL or duplicate values.
• COUNT(1): Under the hood, this behaves exactly the same as COUNT(*) in modern database engines.
• COUNT(column): Counts only rows where the specified column is not NULL.
• COUNT(DISTINCT column): Counts only the unique, non-null values in a column.

Code Examples

SQL

-- Compare COUNT variations
SELECT 
  COUNT(*) AS total_rows,
  COUNT(phone) AS rows_with_phone, -- Skips NULL phone rows
  COUNT(DISTINCT country) AS unique_countries -- Skips NULLs and deduplicates
FROM customers;

Best Practices

• Use COUNT(*) for Row Counts: Avoid specifying specific columns inside COUNT() unless you explicitly want to ignore NULL occurrences. Specifying COUNT(*) is highly optimized by database query plans.

Interview Perspective

Interactive Challenges

Summary

The COUNT function tallies active observations, with options to skip nulls or filter for unique values using DISTINCT.