LearnNowOnline

Blog

T-SQL: Counting Rows & Column Data

Excerpt by Don Kiely | April 25, 2013

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:

SELECT COUNT(*) FROM dbo.Employees

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

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:

SELECT COUNT(*) AS NumEmployees,

COUNT(Region) AS NumRegion

FROM dbo.Employees

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.

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.

This 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.