Each of the examples uses the table of produce items that appears at the bottom of the page.
Counting Records in a TableLet'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(*)This query returns the number of rows in the table: in this case, 5.
Counting Unique Values in a ColumnWe 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)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 CriteriaWe 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(*)In this case, the query would return a value of 1, as only oranges are below the specified stock level.
WHERE Quantity < 5;
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.