How to use COUNT Function in SQL Server

by

In this SQL Server tutorial, you will learn how to use the COUNT function in SQL Server to count the number of rows in a table.

The COUNT() is a basic function for data analysis or another purpose, depending on the requirement. Here, you will understand the COUNT() function variants with syntax.

Also, you will learn how to use the COUNT() function, the HAVING clause, and other aggregate functions.

COUNT Function in SQL Server

The COUNT function is an SQL Server aggregate function that returns the number of rows in the set of records or columns. You can use the COUNT() function in reporting, data validation, and analysis tasks.

Moreover, it helps find the total number of records in a database table based on specific criteria.

The syntax is given below.

COUNT( ALL | DISTINCT expression | * } )

Where,

  • COUNT(): This function helps determine the number of rows in the database table.
  • ALL | DISTINCT expression | * : You can use the COUNT() in three ways as shown below:
    • First, understand the expression; it can be a column name or any image, text, or next. But you can’t use the aggregate function or subqueries in the expression.
    • Second, you can use the COUNT() function as COUNT(ALL expression), COUNT(DISTINCT expression), and COUNT(*).
      • COUNT(ALL expression) returns all non-null expression values (column_name).
      • COUNT(DISTINCT expression) returns only unique non-null expression values (column_name).
      • COUNT(*) returns all rows in a table with NULL and duplicate values in any column.

So, from the above syntax, you know three variants of the COUNT() function. Let’s see, for example, how they work.

COUNT Function in SQL Server with ALL Option

When you use the ALL option with the COUNT function in SQL Server, it returns the number of non-null values of any specified column. This option is the default behavior of the COUNT() function.

For example, suppose you are managing a bank database and have access to the Employees table below.

COUNT Function in SQL Server with ALL Option Table Employees

The Employees table has columns EmployeeID, Name, and Department. I want you to look at the Department column where the employees with IDs 5 and 7 don’t have a department, but the department is NULL for these employees.

So, first, use the below query with option ALL on an EmplyeeID column.

SELECT COUNT(ALL EmployeeID) AS CountEmployees
FROM Employees;
COUNT Function in SQL Server with ALL Option on EmployeeID column

As you can see from the output of the above query in the picture, there are six employees in an Employees table when you have used the COUNT() function with the ALL option on an EmployeeID column.

You need to understand that it counts all the employees, including those with a NULL value in their Department column. This is because there is no null value in the EmployeeId, so it ignores the NULL value of the other column of the table.

From the above, you can get an idea of how the COUNT() function works when you use the option ALL. Again, I want you to use the COUNT() function with the ALL option on the Department column using the query below.

SELECT COUNT(ALL Department) AS CountEmployees
FROM Employees;
COUNT Function in SQL Server with ALL Option on EmployeeID column with Null Values

This time, it returns that there are only 4 employees in the Employees table because, from the definition of the COUNT() function, you know that it can count the non-null value of the column.

So in the Employees table, two employees with ID 5 and 7 have NULL values in the Department column, and other employees have their specified department name (non-null values). That’s why it counts only four employees.

  • Remember, both are the same if you use the COUNT(column_name) instead of COUNT(ALL column_name). In the case of COUNT(column_name), the option ALL is used implicitly or because of the default behavior.

So you can use the above query in two ways to get the same result.

SELECT COUNT(ALL Department) AS CountEmployees
FROM Employees;
            OR

SELECT COUNT(Department) AS CountEmployees
FROM Employees;

This is how you can use the ALL option with the COUNT function in SQL Server to count only the non-null values of the expression or column of the table.

COUNT Function in SQL Server with Distinct Option

If you want to count the unique non-null values in a table column, use the DISTINCT option with the COUNT() function. In other words, you want to count the values of a column that should not be null and unique throughout the rows.

For example, suppose the column name ‘Number’ contains values [1,3,3,7,8, NULL, 10], so here, the COUNT(DISTINCT Number) function counts the value three only once and ignores the NULL value. So, it will return the count as 5.

For example, if you look again at the Employees table below, more than one employee belongs to the same department.

COUNT Function in SQL Server with Distinct Option Employees Table

Now, if you want to know the distinct (unique) department name in the column Department of Employees table, use the below query.

SELECT COUNT(DISTINCT Department) AS CountDistinctDept
FROM Employees;
COUNT Function in SQL Server with Distinct Option For Department Column

When you execute the above query, it returns the result as four, the number of unique department names in the Department column. But in the Department column of the table, there are eight entries (values), including the NULL and duplicate values.

But the COUNT(DISTINCT Department) returns only the count as four because it counts unique and non-null values. So, in the Department column, the two values are NULL, and the 2 are duplicates.

So, in total, it ignores 4 values, and from 8 values, if you deduct 4, you get only 4 as a result. This is how you can count the distinct values in a set of records using the option DISTINCT with COUNT function in SQL Server.

Using the asterisk within the COUNT Function in SQL Server

When you use the COUNT() function as COUNT(*) containing the asterisk (*) symbol, it counts all the rows in a table, including the NULL values in a specified column.

Generally, if you want to find the total number of records in a table, use this COUNT() function variant.

For example, if you want to count the number of employees (records or rows) in a table Employees, then you can use the below query.

SELECT COUNT(*) AS CountDistinctDept
FROM Employees;
Using the asterisk within the COUNT Function in SQL Server

As you can see from the above query output, it returns the total number of records or rows in a table. The number of employees is 8. But here, you haven’t specified any column name, so when you need only to count the number of rows in a table, use this COUNT(* ).

COUNT Function in SQL Server with other Aggregate Function

You can use the COUNT() function with other aggregate functions such as SUM, AVG, MIN, and MAX. Use these aggregate functions with the same query (SELECT statement) on different columns to perform the calculations.

Suppose you have a Products table with columns ProductID, ProductName, Price, Description, and Stock, shown below.

COUNT Function in SQL Server with other Aggregate Function Products Table

Look at the Price column of the Products table that contains the price of each product. Now, suppose you need to find the total number of products and the average price of each product, then you can use the AVG() function on the Price column to calculate that average price.

So use the below query.

SELECT COUNT(*) AS TotalProducts,AVG(Price) as AveragePrice
FROM Products;
COUNT Function in SQL Server with other Aggregate Function

As you can see in the above output, there are 29 products in the Products table, and the average price of each product is 258.955 dollars. The query contains the COUNT() function with another aggregate function, AVG(), to compute the average price.

This is how to use another aggregate function with the COUNT function in SQL Server.

COUNT Function in SQL Server with Having Clause

You can also use the COUNT() function with a clause. For example, suppose you want to find departments with more than a certain number of employees. Using the query below, you can use COUNT() with the HAVING clause.

SELECT Department, Count(*) As NumOfEmp
FROM Employees GROUP BY Department
HAVING COUNT(*) > 1;
COUNT Function in SQL Server with Having Clause

When you execute the above query, it returns a result set containing the department name and employees in that department. As you can see, the HR and IT departments contain two employees each.

So here, you have used the COUNT(*) with the HAVING clause to filter the number of employees for each department; in this case, it filters the department with more than one employee. As a result, two departments, HR and IT, contain more than one employee.

You can use the COUNT() with HAVING clause in SQL Server. But there are different use cases where you can use the COUNT() function, such as:

  • In e-commerce, you can count the number of products in different categories; in healthcare, you can count the number of patients diagnosed with a particular illness; or in finance, you can count the number of transactions per customer.

Conclusion

In this SQL Server tutorial, you learned how to count the number of rows in a table using the COUNT() function in SQL Server. After that, you used the COUNT() function to find the distinct non-null value from the column of the table, as well as the COUNT() function with the AVG() aggregate function and HAVING clause.

You may like to read: