🔢 SQL COUNT() Function – How to Count Records in SQL Effectively

The SQL COUNT() function is one of the most widely used aggregate functions in SQL. Whether you’re tracking customer signups, counting orders, or auditing data completeness, COUNT() helps you quickly summarize the number of rows in a table or a subset of it.

In this guide, you’ll learn:

✅ What the SQL COUNT() function does
💻 Syntax and variations
🔍 Real-life examples with WHERE, GROUP BY, and DISTINCT
🧠 Tips to avoid common mistakes

✅ What is SQL COUNT()?

The COUNT() function returns the number of rows that match a specified condition. It’s most commonly used to count:

Total rows
Rows with a specific condition
Unique values using DISTINCT

🔧 Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

You can also use:

COUNT(*) to count all rows
COUNT(DISTINCT column) to count unique non-null values
COUNT(column) to count non-null values only

🔍 SQL COUNT() Examples

🧪 Example 1: Count All Rows in a Table

SELECT COUNT(*) AS total_customers
FROM customers;

✔️ Returns the total number of rows (including NULL values).

🧪 Example 2: Count Non-NULL Values in a Column

SELECT COUNT(email) AS valid_emails
FROM customers;

✔️ Only counts rows where email is not NULL.

🧪 Example 3: Count with WHERE Clause

SELECT COUNT(*) AS usa_customers
FROM customers
WHERE country = 'USA';

✔️ Filters the count to customers located in the USA.

🧪 Example 4: Count Distinct Values

SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

✔️ Returns the number of unique city names.

🎯 COUNT() with GROUP BY

You can group your results to count rows per category.

Example: Orders per Customer

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

🧠 Great for analyzing activity across different users, regions, or categories.

⚠️ Common Mistakes to Avoid

Mistake	Fix / Explanation
Using `COUNT(column)` on nullable column	Will exclude NULLs – use `COUNT(*)` if you want all rows
Forgetting `DISTINCT`	`COUNT(column)` may include duplicates
Misusing `GROUP BY`	Always group by all non-aggregated fields

📌 Summary Table

Use Case	Syntax	Description
Count all rows	`COUNT(*)`	Includes all rows
Count non-null column values	`COUNT(column)`	Ignores NULLs
Count distinct values	`COUNT(DISTINCT column)`	Unique, non-null values
Count with condition	`COUNT(*) WHERE condition`	Use inside `WHERE` clause

🧠 Real-World Use Cases

E-commerce: Count orders per user or region
Marketing: Count unique email subscribers
HR: Count employees in each department
Finance: Count transactions per account

COUNT Function