T-SQL: Counting Rows & Column Data

One of the primary purposes of a relational database is to summarize information. Other than for data entry, you will rarely look at individual orders. Instead, using the database, you will do things like summarize all the orders by week, month, or year. SQL Server performs these kinds of summaries using aggregate functions. These aggregate functions, combined with the GROUP BY clause, can combine a tremendous amount of data into a clear and concise summary. This article looks at the COUNT aggregate function for counting rows & column data.

Counting Rows

Use the COUNT function to return the number of rows in a table, as shown in the following query. This query uses the asterisk (*) as the value of the argument to COUNT so that it counts all of the rows without considering column values and whether they have NULL values:

The Figure below shows the results of the query, indicating that there are nine employees in that table in the Northwind database.

Counting the number of rows in a table

TIP: Your aggregate queries will execute faster if you use the asterisk in the COUNT function instead of a column name. The asterisk instructs SQL Server to count only the number of rows. Using a column name forces SQL Server to retrieve every value in the column and check for nulls, which aren’t included in the count. If all you’re doing is counting the rows, the asterisk is more efficient.

Counting Column Data

When you specify a column name in the COUNT function, the count excludes any null values in the column. The following query counts the total number of rows in the Employees table, and then counts the non-null values in the Region column:

Because the table contains null Region values, the result set in the Figure below shows a discrepancy between the number of employees and the number of regions.

COUNT aggregate functions do not include null values

As you can see from these results, you’ll want to be careful about whether you use an asterisk or a column name with the COUNT aggregate function (and other aggregate functions). Most of the time an asterisk will work, unless you need to account for NULL values.

ldn-expertdkielyThis post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *