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)
4

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)
3

Next: SQL MAX

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact