T SQL Tutorial : COUNT Function

The TSQL COUNT function is used to count the number of rows returned in a SELECT statement.

The syntax for the TSQL COUNT function is:

SELECT COUNT (expression)

FROM tables

WHERE conditions;

Only includes NOT NULL Values

Not everyone realizes this, but the TSQL COUNT function will only include the records in the count where the value of expression in COUNT (expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.

Let's look at a TSQL COUNT Function example that demonstrates how NULL values are evaluated by the COUNT function.

For example, if you have the following table called Customers:

Customer_id	Customer_name	state
1	RAY	CA
2	Rohan
3	Thomas

And if you ran the following TSQL SELECT statement that uses the TSQL COUNT functions:

SELECT COUNT(State)

FROM Customers;

This TSQL COUNT example will return 3 since all customer_id values in the query's result set are NOT NULL.

However, if you ran the next TSQL SELECT statement that uses the TSQL COUNT functions:

SELECT COUNT(state)

FROM Customers;

This TSQL COUNT example will only return 1, since only one state value in the query's result set is NOT NULL. That would be the first row where the state = 'CA'. It is the only row that is included in the COUNT function calculation.

With Single expression

The simplest way to use the TSQL COUNT function would be to return a single field that returns the COUNT of something. For example, you might wish to know how many employees have a salary that is above $25,000 / year.

SELECT COUNT(*) AS "Number of employees"

FROM employees

WHERE salary > 25000;

In this TSQL COUNT function example, we've aliased the COUNT(*) expression as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Using TSQL DISTINCT Clause

You can use the TSQL DISTINCT clause within the TSQL COUNT function.  For example, the TSQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.

SELECT COUNT(DISTINCT department) AS "Unique departments"

FROM employees

WHERE salary > 25000;

Again, the COUNT(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

Using TSQL GROUP BY Clause   

In some cases, you will be required to use the TSQL GROUP BY clause with the TSQL COUNT function. For example, you could use the TSQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) AS "Number of employees"

FROM employees

WHERE salary > 25000

GROUP BY department;

Because you have listed one column in your TSQL SELECT statement that is not encapsulated in the TSQL COUNT function, you must use the TSQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.