SQL > SQL Functions >
The COUNT function in SQL is used to calculate the number of rows returned from the SQL statement.
The syntax for the COUNT function is,
SELECT COUNT (<expression>)
<expression> can be a column name, an arithmetic operation, or a star (*). When we use COUNT(*), we mean "count everything."
It is also possible to have one or more columns in addition to the COUNT function in the SELECT statement. In those cases, these columns need to be part of the GROUP BY clause as well:
SELECT "column_name1", "column_name2", ... "column_nameN", COUNT (<expression>)
GROUP BY "column_name1", "column_name2", ... "column_nameN";
COUNT is often combined with DISTINCT to calculate the number of unique values. The syntax for this is as follows:
SELECT COUNT (DISTINCT <expression>)
We use the following table for our example.
Example 1: Simple COUNT operation
To find the number of rows in this table, we key in,
Please note that we can also use COUNT(*) instead of COUNT(Store_Name). In this case the two will generate the same answer because none of the values in the Store_Name field is NULL.
Example 2: COUNT function with a GROUP BY clause
To get the number of records for each store, we type in,
SELECT Store_Name, COUNT(*) FROM Store_Information GROUP BY Store_Name;
Example 3: Use COUNT with DISTINCT
COUNT and DISTINCT can be used together in a statement to retrieve the number of distinct entries in a table. For example, if we want to find out the number of distinct stores in the Store_Information table, we type in,
SELECT COUNT (DISTINCT Store_Name)