COUNT Function
You'll learn how to use the COUNT aggregate function to tally rows in your database queries.
What COUNT Does
The COUNT function is one of SQL's most fundamental aggregate functions. It counts the number of rows that match your query criteria. Think of it like a ticket counter at a concert venue—it simply tallies how many entries pass through, giving you a numeric total.
Three Flavors of COUNT
COUNT(*)
This counts all rows in the result set, including those with NULL values in any column. It's the "count everything" approach:
SELECT COUNT(*) FROM employees;
-- Returns total number of employee records
COUNT(column)
This counts only rows where the specified column is NOT NULL. NULLs are ignored:
SELECT COUNT(manager_id) FROM employees;
-- Returns number of employees who have a manager assigned
COUNT(DISTINCT column)
This counts only unique non-NULL values in a column. Duplicates are collapsed into one:
SELECT COUNT(DISTINCT department) FROM employees;
-- Returns number of different departments (each counted once)
Practical Differences
Imagine a table with 100 employees. If 95 have a phone_number but 5 don't (NULL):
COUNT(*)returns 100COUNT(phone_number)returns 95COUNT(DISTINCT phone_number)might return 90 if some numbers are shared
Common Use Cases
- Data validation: How many records exist?
- Business metrics: How many active customers?
- Quality checks: How many rows have missing data?
Key Takeaway: COUNT(*) counts all rows, COUNT(column) counts non-NULL values in that column, and COUNT(DISTINCT column) counts unique non-NULL values—choose based on what you need to measure.