How to Use COUNT in SQL

Use COUNT() when you need to know how many rows match a condition. This is the standard SQL aggregate for dashboards, analytics, validation checks, and reporting.

What you’ll build or solve

You’ll learn how to use COUNT in SQL for total rows, filtered rows, and distinct values. You’ll also know how NULL values affect counting.

When this approach works best

This approach is the right choice when the goal is totals instead of full row details.

Common real-world scenarios include:

• Total users
• Paid orders
• Unique countries
• Error log totals
• Signup conversion reporting

This is a bad idea when you need the actual row details. In that case, use SELECT without aggregation.

Prerequisites

You only need:

• A SQL table with rows
• Basic SELECT and WHERE knowledge

Step-by-step instructions

Step 1: Wrap the target in COUNT()

Use COUNT(*) to count all matching rows.

SELECT COUNT(*)
FROM users;

This returns the total number of rows in the table.

Add WHERE to count only matching rows.

SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

To count unique values, combine it with DISTINCT.

SELECT COUNT(DISTINCT country)
FROM customers;

What to look for:

• COUNT(*) counts rows
• WHERE filters before counting
• COUNT(DISTINCT ...) counts unique values
• Great for dashboards and checks
• COUNT(column) ignores NULL

Examples you can copy

Total products

SELECT COUNT(*)
FROM products;

Paid orders

SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

Unique countries

SELECT COUNT(DISTINCT country)
FROM customers;

Common mistakes and how to fix them

Mistake 1: Using COUNT(column) expecting all rows

What the reader might do:

SELECT COUNT(phone_number)
FROM users;

Why it breaks: rows where phone_number is NULL are excluded.

Corrected approach:

SELECT COUNT(*)
FROM users;

Mistake 2: Forgetting DISTINCT for unique totals

What the reader might do:

SELECT COUNT(country)
FROM customers;

Why it breaks: duplicate countries are counted multiple times.

Corrected approach:

SELECT COUNT(DISTINCT country)
FROM customers;

Mistake 3: Mixing row details with aggregates incorrectly

What the reader might do:

SELECT email, COUNT(*)
FROM users;

Why it breaks: this needs GROUP BY.

Corrected approach:

Add grouping when mixing aggregates and normal columns.

Troubleshooting

If totals seem too low, check for NULL values in COUNT(column).

If unique counts look inflated, add DISTINCT.

If the query errors with normal columns, use GROUP BY.

If the result should include filters, verify the WHERE clause.

Quick recap

• Use COUNT(*) for total rows
• Add WHERE for filtered counts
• Use COUNT(DISTINCT ...) for unique values
• COUNT(column) ignores NULL
• Use GROUP BY with non-aggregated columns