SQL COUNT

SQL > SQL Functions > 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

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

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:

COUNT (Store_Name)

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:

Store_Name	COUNT(*)
Los Angeles	2
San Diego	1
Boston	1

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:

COUNT (DISTINCT Store_Name)

Next: SQL MAX

This page was last updated on June 19, 2023.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.