COUNT() Function in SQL

The COUNT() function in SQL is one of the most commonly used SQL aggregate functions which helps users quickly determine the number of rows or values that satisfy a given condition. This function makes SQL data analysis, database reporting and record counting much easier.

What is the COUNT() Function in SQL?

The COUNT() function in SQL is an aggregate function that returns the total number of rows or values in a table. It is commonly used with the SELECT statement to count records in a database table.

The function can count:

• Every row in a table.
• Non-NULL values in a specific column.
• Unique values using the DISTINCT keyword.
• Records that satisfy a particular condition using the WHERE clause.

Since COUNT() performs calculations on multiple rows and returns a single result, it belongs to the family of SQL aggregate functions along with SUM(), AVG(), MIN() and MAX().

Syntax

The COUNT() function can be written in different ways depending on what you want to count.

Count All Rows

Count Non-NULL Values

Count Unique Values

Parameters

*: The asterisk tells SQL to count every row available in the table.

column_name: It specifies the column whose non-NULL values should be counted. Any NULL values present in the column are ignored.

DISTINCT: This keyword removes duplicate values before counting them and the unique values are included in the final count.

table_name: It represents the database table from which the records are counted.

Return Value

The COUNT() function returns a single integer value representing the total number of rows or values counted and the returned value is always a numeric value.

The function returns 0 when no rows satisfy the specified condition or when no non-NULL values are found in the counted column.

Examples of COUNT() Function in SQL

We will be using the following table called Employees to be able to understand the COUNT() function in SQL properly.

Table: Employees

Example 1: Count All Records in the Table

We will comprehend here how to use the COUNT() function in SQL to count the total number of rows or records available in a database table. It is one of the most common SQL aggregate function operations and is a widely used technique to quickly determine the total number of stored rows.

SQL Query:

Output:

Explanation:

The COUNT() function returned 9 as the final result which tells us there are a total of nine employee records stored in the Employees table.

Example 2: Count Values in a Specific Column

In this example, we will learn how to use the COUNT() function to count the number of values available in a specific column. It demonstrates how SQL aggregate functions count only the non-NULL values of a selected column which makes it useful for SQL data analysis, database reporting and record validation.

SQL Query:

Output:

Explanation:

The COUNT() function returned the result as 9 which indicates that the Department column contains a value for every employee record and also has no NULL values.

Example 3: Count Records That Match a Condition

In this example, we will learn how to use the COUNT() function together with the WHERE clause to count only those records that satisfy a specific condition. It is a common technique to find the number of records that meet particular criteria.

SQL Query:

Output:

Explanation:

The COUNT() function counts only those employee records where the Department is Engineering. It returned the count as 4 as there are only four employees who belong to the specified department.

Example 4: Count Unique Values Using DISTINCT

In this example, we will learn how to use the COUNT() function with the DISTINCT keyword to count unique values in a column. This method removes duplicate values before counting them.

SQL Query:

Output:

Explanation:

The COUNT(DISTINCT Country) function returns 3 unique countries as the table contains employees from the USA, India and Taiwan.

Example 5: Count Records Based on Salary

We will understand here how to use the COUNT() function with the WHERE clause to count employees whose salary is greater than a specified value. This approach is frequently used to analyze records that satisfy numeric conditions.

SQL Query:

Output:

Explanation:

The COUNT() function counts only those employees whose salary is greater than 230000.00. Employee records satisfy this condition based on the available data so the function returns 5.