SQL stands for Structured Query Language; a very important part of SQL is The Query element. It retrieves the data based on the particular criteria from the relational database. This retrieval can be accomplished using the COUNT function, which is, when paired with the specific column of the database, yields all types of information.
The SQL COUNT function is specifically very useful as the count’s database records are based on the user-specified criteria. Use this criterion to count all the records in the table, count the column’s unique values, or count the number of times records occur to meet the particular criteria.
Example
Northwind Database
This example is based on the commonly used Northwind database, which is frequently ships with the database products for using it as a tutorial. Now, look at the excerpt from the database Product table:
| Product ID | Product Name | Supplier ID | Quantity Per Unit | Unit Price | units in Stock |
| 1 | Chai | 1 | Ten boxes x 20 bags | 18.00 | 39 |
| 2 | Chang | 1 | 24 – 12 oz bottles | 19.00 | 17 |
| 3 | Aniseed Syrup | 1 | 12 – 550 ml bottles | 10.00 | 13 |
| 4 | Chef Anton’s Cajun Seasoning | 2 | 48 – 6 oz jars | 22.00 | 53 |
| 5 | Chef Anton’s Gumbo Mix | 2 | 36 boxes | 21.35 | 0 |
| 6 | Grandma’s Boysenberry spread | 3 | 12 – 8 oz jars | 25.00 | 120 |
| 7 | Uncle Bob’s Organic Dried Pears | 3 | 12 – 1 lb Pkg | 30.00 | 15 |
Product Table
- Counting Records in The Table
The most basic query is to count the number of records in the table. So, to calculate the number of items in a product table, will use the query:
SELECT COUNT(*)
FROM product;
This query will return to the number of rows in the table. It is seven, in this particular example.
- Counting The Unique Values in a Column
Use the COUNT function to identify the number of unique values in the column. In this example, write a query to identify the number of different suppliers whose products appear in the produce department, then execute the given query:
SELECT COUNT (DISTINCT supplier ID)
FROM product;
This query will directly return to the number of different values that are found in the SupplierID column. In this case, the answer is Three, which represents rows 1,2, and 3.
- Counting Records Matching The Criteria
In this query, combine the COUNT function with the WHERE clause to identify several records that match certain criteria. For Instance, suppose the department manager needs to get a sense of the department’s stock levels. The following query determines the number of rows that represents UnitsInStock less than 50 units:
SELECT COUNT (*)
FROM product
WHERE UnitsInStock < 50
In this case, the query will then return to a value of four representing Chai, Chang, Aniseed Syrup, and Uncle Bob’s Organic Dried Pears.
The COUNT clause is very valuable to the database administrators who seek to summarize the data to meet the business requirements. With little creativity, you can then use the COUNT function for a wide range of purposes.
