SqlDatabaseBeginner

COUNT()

Counts rows (COUNT*) or non-NULL values in a column (COUNT(column)).

Review the syntaxStudy the examplesOpen the coding app
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column) FROM table_name;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Counts rows (COUNT*) or non-NULL values in a column (COUNT(column)).

The COUNT() function is a fundamental SQL aggregate operation used to return the number of rows that match a specific criterion. It serves two primary purposes depending on its argument: COUNT(*) counts every row in the result set, including those with NULL values and duplicates, because it targets the row structure itself. Conversely, COUNT(expression) or COUNT(ame) only increments the counter for rows where the evaluated expression results in a non-NULL value. This distinction is critical for data integrity in reporting. Performance-wise, most modern database optimizers treat COUNT(*) as the most efficient way to retrieve row counts, often utilizing metadata or index-only scans. However, COUNT(DISTINCT column) is significantly more expensive as it requires a temporary sort or hash operation to identify unique values. It is also important to note that COUNT() is often used with the GROUP BY clause to provide subtotals for specific categories, and unlike SUM(), it returns 0 when no rows are found rather than NULL.

Quick reference

Syntax

SELECT COUNT(*) FROM table_name;
SELECT COUNT(column) FROM table_name;

Inputs

Parameters

* or columnUse * to count rows, or a column to count non-NULL values.

See it in practice

Examples

1

Counting Total Rows in a Table

SELECT COUNT(*) AS total_employees FROM employees;
Output:
total_employees: 500

Uses COUNT(*) to retrieve the total number of rows in the employees table, regardless of NULL values.

2

Counting Non-Null Values

SELECT COUNT(manager_id) AS assigned_managers FROM employees;
Output:
assigned_managers: 485

This query only counts rows where d is not NULL. If 15 employees do not have a manager, they are excluded from this result.

3

Counting Unique Values with Grouping

SELECT department_id, COUNT(DISTINCT job_id) AS unique_roles
FROM employees
GROUP BY department_id;
Output:
department_id: 10, unique_roles: 3 department_id: 20, unique_roles: 2

Calculates the number of unique job titles within each department. It uses GROUP BY to partition the data and DISTINCT to ignore duplicate job titles within those groups.

Debug faster

Common Errors

1

LogicError

Cause: Using COUNT(column_name) when the intention is to count all rows, leading to undercounting if the column contains NULLs.

Fix: Use COUNT(*) if you want to count every row regardless of content.

SELECT COUNT(email) FROM users; -- Missing users without emails
2

SyntaxError

Cause: Attempting to filter aggregated results using the WHERE clause instead of the HAVING clause.

Fix: Use the HAVING clause to filter groups based on the result of an aggregate function.

SELECT dept_id FROM staff WHERE COUNT(*) > 5 GROUP BY dept_id; -- Should use HAVING

Runtime support

Compatibility

All SQL databasesSQL-92 (Standard)

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Counts rows (COUNT*) or non-NULL values in a column (COUNT(column)).

* or column: Use * to count rows, or a column to count non-NULL values.

LogicError: Use COUNT(*) if you want to count every row regardless of content. SyntaxError: Use the HAVING clause to filter groups based on the result of an aggregate function.