Counting Values in a Database Table with the SQL COUNT Function

The COUNT() function in SQL allows you to count database records based upon a variety of criteria. You can use it to count all records in a table, count unique values in a column or count the number of times records occur that meet certain criteria. This tutorial takes a brief look at each of these scenarios.

Each of the examples uses the table of produce items that appears at the bottom of the page.

Counting Records in a Table

Let's begin with the most basic query: counting the number of records in the table. If we wanted to know the number of items that exist in our inventory table, we could use the following query:

SELECT COUNT(*)
FROM Produce;

This query returns the number of rows in the table: in this case, 5.

Counting Unique Values in a Column

We can also use the COUNT function to identify the number of unique values in a column. For example, suppose we wanted to identify the number of different colors appearing in our produce department. We could accomplish this using the following query:

SELECT COUNT(DISTINCT color)
FROM Produce;

This query returns the number of distinct values found in the color column: in this case, 4, representing red, yellow, orange and green.

Counting Records Matching Criteria

We can combine the COUNT() function with the WHERE clause to identify the number of records that match certain criteria. For example, suppose the produce department manager wanted to get a sense of the stock levels in the department. The following query would identify the number of rows representing inventory levels less than 5 units:

SELECT COUNT(*)
FROM Produce
WHERE Quantity < 5;

In this case, the query would return a value of 1, as only oranges are below the specified stock level.

The COUNT() clause can be extremely valuable to database administrators seeking to summarize data to meet business requirements. In this article, we saw how it can be used to count the number of rows in a table, identify the number of unique values in a column and identify the number of records matching certain criteria. With a little creativity, you can use the COUNT() function for a wide variety of purposes.