SQL COUNT Function – Complete Guide with Examples (2026-27)

✅ What is the SQL COUNT Function?

The SQL COUNT() function is an aggregate function that counts the number of rows in a table or in a group. It is used heavily in reporting, analytics, and everyday database queries.

✅ COUNT(*) — counts all rows including those with NULL values and duplicates.

✅ COUNT(column) — counts only rows where the specified column is NOT NULL.

✅ COUNT(DISTINCT column) — counts only unique non-NULL values in the column.

✅ COUNT is supported in all databases — MySQL, PostgreSQL, SQL Server, Oracle, and SQLite — with identical syntax.

✅ Sample Employees Table – used in all examples below:-

Note: RAJ (ID 3) and MEENA (ID 5) have NULL in the Email column — key for COUNT(Email) examples.

✅ SQL COUNT Syntax – All Three Forms

Form 1 – COUNT(*): Count All Rows

SELECT COUNT(*)
FROM table_name
WHERE condition;

Form 2 – COUNT(column): Count Non-NULL Values Only

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Form 3 – COUNT(DISTINCT column): Count Unique Values

SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE condition;

✅ COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) – Quick Comparison

✅ Example 1 – COUNT(*): Count All Rows in the Table

Find the total number of employees — every row including those with NULL values:

✅ SELECT Query:-

SELECT COUNT(*) FROM Employees;

✅ Result Show:-

✅ Example 2 – COUNT(column): Count Non-NULL Values Only

Count how many employees have an email address on file. Rows where Email is NULL are automatically skipped:

✅ SELECT Query:-

SELECT COUNT(Email) AS EmployeesWithEmail
FROM Employees;

✅ Result Show:-

✅ Example 3 – COUNT with Alias (AS)

Without an alias the result column header shows COUNT(*) — hard to read. Use AS to name it clearly:

✅ SELECT Query:-

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

✅ Result Show:-

✅ Multiple COUNT values in one query:-

SELECT
  COUNT(*)                    AS TotalRows,
  COUNT(Email)                AS HaveEmail,
  COUNT(*) - COUNT(Email)     AS MissingEmail
FROM Employees;

✅ Result Show:-

✅ Example 4 – COUNT with WHERE Clause

Use WHERE to filter rows before counting. Only matching rows are counted:

✅ Example – Count only IT department employees:-

SELECT COUNT(*) AS IT_Employees
FROM Employees
WHERE Department = 'IT';

✅ Result Show:-

✅ Example – Count employees with salary greater than 8000:-

SELECT COUNT(*) AS HighEarners
FROM Employees
WHERE Salary > 8000;

✅ Result Show:-

✅ Example 5 – COUNT(DISTINCT): Count Unique Values

Count how many unique departments exist — IT appears 3 times but is counted only once:

✅ SELECT Query:-

SELECT COUNT(DISTINCT Department) AS UniqueDepartments
FROM Employees;

✅ Result Show:-

✅ Example – Count unique cities:-

SELECT COUNT(DISTINCT City) AS UniqueCities
FROM Employees;

✅ Result Show:-

✅ Example 6 – COUNT with GROUP BY

Use GROUP BY to count rows per category — the most widely used COUNT pattern in real-world SQL:

✅ Example – Count employees per department:-

SELECT Department,
       COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;

✅ Result Show:-

✅ Example – Count employees per city:-

SELECT City,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY City
ORDER BY EmployeeCount DESC;

✅ Result Show:-

✅ Example 7 – COUNT with HAVING

Use HAVING to filter groups after GROUP BY — show only groups where the count meets a condition:

✅ Example – Departments with more than 1 employee:-

SELECT Department,
       COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1
ORDER BY TotalEmployees DESC;

✅ Result Show:-

✅ Example – Cities with exactly 1 employee:-

SELECT City,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY City
HAVING COUNT(*) = 1;

✅ Result Show:-

✅ Example 8 – COUNT with Multiple Aggregate Functions

Combine COUNT with MIN, MAX, and AVG in one query to build a complete department summary report:

✅ SELECT Query:-

SELECT
  Department,
  COUNT(*)    AS TotalEmployees,
  MIN(Salary) AS LowestSalary,
  MAX(Salary) AS HighestSalary,
  AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;

✅ Result Show:-

✅ Key Points to Remember

✅ COUNT(*) counts all rows — including NULLs and duplicates. Use it for total row count.

✅ COUNT(column) skips NULLs — only rows where the column has a value are counted.

✅ COUNT(DISTINCT column) counts unique values — duplicates counted only once.

✅ Always use AS to name the result — COUNT(*) AS TotalEmployees for readable reports.

✅ WHERE filters before counting — limits which rows COUNT sees.

✅ GROUP BY counts per group — one count per department, city, product, etc.

✅ HAVING — not WHERE — filters count results — HAVING COUNT(*) > 2 after GROUP BY.

✅ COUNT always returns an integer — never NULL. Empty results return 0.

✅ Works in all databases — MySQL, PostgreSQL, SQL Server, Oracle, SQLite — identical syntax.

✅ Common SQL COUNT Mistakes to Avoid