Introduction
In data analysis and reporting, one of the most common questions is: "How many items are there?"
- How many customers registered this month?
- How many orders were placed today?
- How many employees work in the Sales department?
- How many customers provided a valid contact phone number?
SQL answers all these questions through the COUNT aggregate function.
What Is COUNT?
COUNT is an aggregate function. Instead of returning multiple individual rows, it reads a set of rows and summarizes them into a single numeric count.
FROM customers;
If your table has 500 customers, this query returns a single row containing the number 500.
COUNT(*) — Counting Rows
COUNT(*) counts total rows in the result set. It does not look at any specific column, and it counts every row regardless of whether individual columns contain NULL.
FROM employees;
Row 2 (Priya, 48k, NULL) ✓
Row 3 (Amit, 85k, 9123456789) ✓
Row 4 (Sneha, NULL, NULL) ✓
(All rows counted)
COUNT(column) — Counting Non-NULL Values
When you pass a specific column name into COUNT, such as COUNT(phone), SQL counts only rows where that column has a non-NULL value.
FROM customers;
COUNT(*) when you want to know how many rows exist. Use COUNT(column) when you want to know how many records have a value in that column.phone = NULL ✗ (Skipped)
phone = '9123456789' ✓ (Counted)
phone = NULL ✗ (Skipped)
(Only 2 non-NULL phones)
COUNT(*) vs COUNT(column) Side-by-Side
Consider the following 4-row customer sample table:
| id | name | phone |
|---|---|---|
| 1 | Aarav | 9876543210 |
| 2 | Diya | NULL |
| 3 | Ishaan | 9123456789 |
| 4 | Riya | NULL |
Counts all 4 customer rows in the table.
Counts only the 2 customers with non-NULL phone numbers.
COUNT(DISTINCT column)
To count only unique, distinct non-NULL values, prefix the column with DISTINCT:
FROM customers;
If your table has 5 rows with cities: ['Mumbai', 'Mumbai', 'Delhi', 'Pune', 'Pune'], COUNT(DISTINCT city) returns 3 (Mumbai, Delhi, Pune).
Counts every row (including NULLs)
Counts non-NULL values
Counts unique non-NULL values
COUNT With WHERE
Combining COUNT with WHERE allows you to calculate filtered totals:
FROM employees
WHERE department = 'Sales';
Mental Execution Flow:
- The database scans the
employeestable. - The
WHERE department = 'Sales'condition filters for only sales personnel. COUNT(*)counts how many rows passed that filter.- A single summary number is returned.
COUNT and NULL In-Depth
Understanding how NULL interacts with counting functions prevents critical business metric errors:
| Syntax | How NULL is Handled | Duplicates Handled | Primary Use Case |
|---|---|---|---|
COUNT(*) | Included (Counts the row) | Included | Total record count in table / group |
COUNT(col) | Ignored (NULL rows skipped) | Included | Count of recorded / completed entries |
COUNT(DISTINCT col) | Ignored (NULL skipped) | Deduplicated | Count of unique non-NULL categories / entities |
COUNT With Column Aliases
By default, aggregate queries return unnamed column headers like count. Use AS to assign intuitive column names:
FROM customers;
Understanding COUNT Results
Without a GROUP BY clause, COUNT always collapses all matching rows into a single scalar row. Even if 1,000,000 rows match your filter, the response is exactly 1 row containing the total number.
| # | name | city | phone | Count Status |
|---|---|---|---|---|
| 1 | Aarav Patel | Mumbai | 9876543210 | ✓ Row counted by COUNT(*) |
| 2 | Diya Sharma | Delhi | NULL | ✓ Row counted by COUNT(*) |
| 3 | Ishaan Verma | Mumbai | 9123456789 | ✓ Row counted by COUNT(*) |
| 4 | Riya Gupta | Pune | 9876543210 | ✓ Row counted by COUNT(*) |
| 5 | Kabir Singh | Bengaluru | NULL | ✓ Row counted by COUNT(*) |
| 6 | Ananya Roy | Delhi | 9988776655 | ✓ Row counted by COUNT(*) |
| 7 | Rohan Mehta | Mumbai | NULL | ✓ Row counted by COUNT(*) |
| 8 | Tara Nair | Pune | 9845123456 | ✓ Row counted by COUNT(*) |
SELECT COUNT(*) AS total_customers FROM customers;
total_customersCommon COUNT Mistakes
If you write COUNT(phone) thinking it counts all users, any user with a NULL phone will be missing from your total count. Always use COUNT(*) for total row counts.
COUNT(city) returns total city entries (including duplicates). COUNT(DISTINCT city) deduplicates and counts unique cities.
Omitting WHERE aggregates across the entire table. Always filter first when counting specific departments, statuses, or date ranges.
Practical Step-by-Step Exercises
| Task Goal | Target Table | Required SQL Solution | Pattern Used |
|---|---|---|---|
| 1. Count All Customers | customers | SELECT COUNT(*) FROM customers; | Total row count |
| 2. Count Valid Phone Numbers | customers | SELECT COUNT(phone) FROM customers; | Non-NULL count |
| 3. Count Unique Customer Cities | customers | SELECT COUNT(DISTINCT city) FROM customers; | Distinct non-NULL count |
| 4. Count Sales Department Staff | employees | SELECT COUNT(*) FROM employees WHERE department = 'Sales'; | WHERE + COUNT |
| 5. Unique Cities With Active Sales | employees | SELECT COUNT(DISTINCT city) FROM employees WHERE department = 'Sales'; | WHERE + COUNT(DISTINCT) |
COUNT Best Practices
- Default to COUNT(*): Use
COUNT(*)for general row counts. Database optimizers are specifically tuned to executeCOUNT(*)efficiently. - Always Alias Aggregate Results: Use
AS total_ordersto make downstream API and reporting tools readable. - Be Conscious of NULL Semantics: Remember that
COUNT(col)silently drops NULL rows. - Combine With WHERE for Segment Counts: Apply filters before aggregation to calculate active, verified, or pending records cleanly.
What You Should Know Now
- ✓COUNT(*): Counts all rows including NULLs
- ✓COUNT(col): Counts non-NULL values in that column
- ✓COUNT(DISTINCT col): Counts unique non-NULL values
- ✓WHERE + COUNT: Filters rows before counting
- ✓Aliases: Gives meaningful names to aggregate output
- ✓Scalar Output: Produces a single numeric summary
🎯 Knowledge Check Quiz: SQL COUNT
Test your understanding of row counting, non-NULL aggregation, and distinct counting patterns.