Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

Back to Library

COUNT() in SQL

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

Syntax

COUNT(*) -- counts all rows 
COUNT(column) -- counts non-null values 
COUNT(DISTINCT column) -- counts unique non-null values

Return type

INTEGER/BIGINT (depends on DBMS)

COUNT() Function Example

SELECT COUNT(*) FROM employees; 
-- Output: 100 (total number of rows)

What is COUNT() in SQL?

The COUNT() function in SQL is used to count the number of rows or non-null values in a specified column. It is commonly used for aggregating data, performing statistical analysis, and generating summary reports.

This function is supported in SQL Server, MySQL, PostgreSQL, and Oracle.

The COUNT() function is widely used in data aggregation, reporting, and business intelligence.

Parameters:

(asterisk): Count all rows
column: Column name to count non-null values
DISTINCT column: Column name to count unique values

Example Use Cases:

-- Count total employees

SELECT COUNT(*) AS total_employees FROM employees; 
-- Output:
total_employees
---------------
         1,250

-- Count filled positions

SELECT COUNT(manager_id) AS positions_filled FROM departments; 
-- Output:

department_name    manager_id    positions_filled
---------------   ----------    ----------------
Sales             101                  1
Marketing         102                  1
Engineering       103                  1
HR                NULL                 0
Finance           105                  1
IT                106                  1
Operations        NULL                 0
                                    ------
Total positions filled:                 5

-- Count unique product categories

SELECT COUNT(DISTINCT category) AS unique_categories FROM products;
-- Output:
category          count
---------------   -----
Electronics          1
Clothing             1
Books                1
Home & Garden        1
Sports               1
Toys                 1
                 ------
unique_categories:    6

Notes:

Behavior: COUNT(*) includes NULL values, COUNT(column) excludes NULLs
Performance Considerations: COUNT(*) often optimized for better performance
Version Info: Core aggregation function available in all major DBMS
Deprecated/Recommended Alternatives: None

Error Handling:

Error: Returns 0 for empty result sets
Recommendation: Use COALESCE when combining with other operations

Supported Databases:

DBMS

Function / Syntax

Example

Includes in *, excludes in column

Related Functions:

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

AVG()

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

MIN()

Returns the smallest value from a specified column or expression, ignoring NULL values.

MAX()

Returns the largest value from a specified column or expression, ignoring NULL values.

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

COUNT()

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

Get Sherloq Free

COUNT() in SQL

Syntax

Return type

COUNT() Function Example

What is COUNT() in SQL?

Parameters:

Example Use Cases:

Notes:

Error Handling:

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

PostgreSQL

SQL Server

SQLite

BigQuery

Snowflake

Athena

Related Functions:

SUM()

AVG()

MIN()

MAX()

STDEV()

VARIANCE()

STRING_AGG()

GROUP_CONCAT()

COUNT()

One place for all your queries, directly on your SQL editor

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

One place for all your queries,
directly on your SQL editor