SQL COUNT

The COUNT function in SQL is used to calculate the number of rows returned from the SQL statement.

Syntax

The syntax for the COUNT function is,

SELECT COUNT (<expression>)
FROM "table_name";

<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>)
FROM "table_name";
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>)
FROM "table_name";

Examples

We use the following table for our examples.

Table Store_Information

Example 1: Simple COUNT operation

To find the number of rows in this table, we key in,

SELECT COUNT(Store_Name)
FROM Store_Information;

Result:

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;

Result:

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)
FROM Store_Information;

Result: