Counting Values in a Database Table With the SQL COUNT Function

Use SQL COUNT to returns a wide range of data

The queries element is an important part of Structured Query Language (SQL). It retrieves data based on specific criteria from a relational database. You can use SQL queries – including the COUNT() function – to obtain all sorts of information from a database.

The SQL COUNT() function is particularly useful because it allows you to count database records based on user-specified criteria. You can use it to count all the records in a table, count unique values in a column, or count the number of times records occur that meet certain criteria.

This article takes a brief look at each of these scenarios.

The examples are based on the commonly used Northwind database, which frequently ships with database products for use as a tutorial.

Here's an excerpt from the database's Product table: 

Product Table
ProductIDProductNameSupplierIDQuantityPerUnitUnitPriceUnitsInStock
1Chai110 boxes x 20 bags18.0039
2Chang124 - 12 oz bottles19.0017
3Aniseed Syrup112 - 550 ml bottles10.0013
4Chef Anton's Cajun Seasoning248 - 6 oz jars22.0053
5Chef Anton's Gumbo Mix236 boxes21.350
6Grandma's Boysenberry Spread312 - 8 oz jars25.00120
7Uncle Bob's Organic Dried Pears312 - 1 lb pkgs.30.0015

 

Counting Records in a Table

The most basic query is counting the number of records in the table. If you want to know the number of items that exist in a product table, use the following query:

SELECT COUNT(*)
FROM product;

This query returns the number of rows in the table. In this example, it is 7.

Counting Unique Values in a Column

You can also use the COUNT function to identify the number of unique values in a column. In the example, if you want to identify the number of different suppliers whose products appear in the produce department, you could accomplish this using the following query:

SELECT COUNT(DISTINCT SupplierID)
FROM product;

This query returns the number of distinct values found in the SupplierID column. In this case, the answer is 3, representing 1, 2, and 3.

Counting Records Matching Criteria

Combine the COUNT() function with the WHERE clause to identify the number of records that match certain criteria. For example, suppose the department manager wants to get a sense of the stock levels in the department. The following query identifies the number of rows representing UnitsInStock less than 50 units:

SELECT COUNT(*)
FROM product
WHERE UnitsInStock < 50;

In this case, the query would return a value of 4, representing Chai, Chang, Aniseed Syrup, and Uncle Bob's Organic Dried Pears.

The COUNT() clause can be extremely valuable to database administrators who seek to summarize data to meet business requirements. With a little creativity, you can use the COUNT() function for a wide variety of purposes.

Was this page helpful?