COUNT Function - SQL Server

The COUNT function returns the number of rows in a query.

The syntax for the COUNT function is:

  <code>  SELECT COUNT(expression)
    FROM tables
    WHERE predicates;</code>


Note:

The COUNT function will only count those records in which the field in the brackets is NOT NULL.

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

  <code>  Supplier_ID     Supplier_Name     State
    1     IBM     CA
    2     Microsoft    
    3     NVIDIA      </code>

The result for this query will return 3.

<code>    Select COUNT(Supplier_ID) from suppliers;</code>

While the result for the next query will only return 1, since there is only one row in the suppliers table where the State field is NOT NULL.

   <code> Select COUNT(State) from suppliers;</code>

Simple Example

For example, you might wish to know how many employees have a salary that is above $25,000 / year.

   <code> SELECT COUNT(*) as "Number of employees"
    FROM employees
    WHERE salary &gt; 25000;</code>

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

Example using DISTINCT

You can use the DISTINCT clause within the COUNT function.

For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.

 <code>   SELECT COUNT(DISTINCT department) as "Unique departments"
    FROM employees
    WHERE salary &gt; 25000;</code>

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

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the COUNT function.

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

   <code> SELECT department, COUNT(*) as "Number of employees"
    FROM employees
    WHERE salary &gt; 25000
    GROUP BY department;</code>

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

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

  <code>  SELECT department, COUNT(1) as "Number of employees"
    FROM employees
    WHERE salary &gt; 25000
    GROUP BY department;</code>

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.