SQL COUNT() function

Last update on June 28 2024 08:31:14 (UTC/GMT +8 hours)

COUNT() function

The SQL COUNT() function returns the number of rows that match a specified condition. It is commonly used to determine the number of entries in a table, count distinct values, or count rows after filtering data. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.

Syntax:

COUNT(*)
COUNT( [ALL|DISTINCT] expression )

The above syntax is the general SQL 2003 ANSI standard syntax. This helps to understand the way SQL COUNT() Function is used. But different database vendors may have different ways of applying COUNT() function.

Bellow, you can see that MySQL, PostgreSQL, and Microsoft SQL Server follows the same syntax as given above. But DB2 and Oracle differs slightly.

Overall, you can use * or ALL or DISTINCT or some expression along with COUNT to COUNT the number of rows w.r.t. some condition or all of the rows, depending up on the arguments you are using along with COUNT() function.

DBMS Support: COUNT() function

DBMS	Command	Notes
MySQL	Supported	Follows standard SQL syntax
PostgreSQL	Supported	Follows standard SQL syntax
SQL Server	Supported	Follows standard SQL syntax
Oracle	Supported (with window clause)	Allows use of COUNT with window functions
DB2	Supported (with window clause)	Allows use of COUNT with window functions

DB2 and Oracle Syntax:

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

Parameters:

Name	Description
COUNT(ALL expression):	Counts all non-NULL values for the specified expression (default behavior).
COUNT(DISTINCT expression):	Ignored duplicate values and counts only unique non-NULL values for the specified expression.
expression	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values. Expression of any type except text or image. Aggregate functions and subqueries are not permitted.
*COUNT()**	Counts all rows in the table, including rows with NULL values.

Syntax diagram - COUNT() function

In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

An important thing about COUNT() function:

When the * is used for COUNT(), all records ( rows ) are COUNTed if some content NULL but COUNT(column_name) does not COUNT a record if its field is NULL.

Examples:

Count unique non-NULL values in the 'agent_code' column:

SQL Code:


-- Counting the number of unique agent codes in the 'orders' table
SELECT COUNT(DISTINCT agent_code)
-- From the 'orders' table
FROM orders;

Explanation:

This SQL query counts the number of unique agent_code values in the orders table.

The COUNT function, combined with the DISTINCT keyword, ensures that only unique agent_code values are counted.

This is useful for determining how many different agents are represented in the orders table.

Count all non-NULL values in the 'advance_amount' column:

SQL Code:


-- Counting all non-NULL values in the 'advance_amount' column of the 'orders' table
SELECT COUNT(ALL advance_amount)
-- From the 'orders' table
FROM orders;

Explanation:

This SQL query counts all non-NULL values in the advance_amount column of the orders table.

The ALL keyword is optional and the default behavior in SQL, ensuring that all non-NULL advance_amount values are included in the count.

This is useful to determine how many entries have a specified advance_amount.

Example for DB2:

SQL Code:


-- Counting the total number of rows in the 'orders' table,
-- partitioned by each unique 'agent_code'
SELECT COUNT(*) 
    -- Using the OVER clause to partition the count by 'agent_code'
    OVER (PARTITION BY agent_code)
-- From the 'orders' table
FROM orders;

Explanation:

This SQL query counts the total number of rows in the orders table for each unique agent_code.

The PARTITION BY clause segments the data into partitions based on the agent_code, and the COUNT(*) function calculates the number of rows within each partition.

This effectively provides a count of orders grouped by each agent without collapsing the result into a single row per group, allowing for detailed analysis of order distribution by agent.

Example for Oracle:

SQL Code:


-- Counting the cumulative number of rows in the 'orders' table,
-- ordered by 'ord_date'
SELECT COUNT(*)
    -- Using the OVER clause to apply the count in an ordered manner by 'ord_date'
    OVER (ORDER BY ord_date)
-- From the 'orders' table
FROM orders;

Explanation:

This SQL query counts the cumulative number of rows in the orders table as the rows are ordered by the ord_date column.

The ORDER BY clause within the OVER function specifies that the counting should follow the order of the ord_date.

This produces a running total of orders, which shows how the count accumulates over time based on the order dates.

Each row in the result set will display the count of all preceding rows, including itself, sorted by ord_date.

Counting Rows with Joins::

SQL Code:


-- Counting the total number of rows resulting from joining the 'orders' table with the 'customers' table
SELECT COUNT(*)
-- From the 'orders' table
FROM orders
-- Performing an INNER JOIN on the 'orders' and 'customers' tables
-- Joining the tables based on matching values in the 'cust_code' columns of both tables
JOIN customers ON orders.cust_code = customers.cust_code;

Explanation:

This SQL query counts the total number of rows that result from an inner join between the orders and customers tables, using the cust_code column as the joining key.

The JOIN clause combines rows from both tables where there is a matching cust_code, and the COUNT(*) function then counts all the resulting rows from this combined dataset.

This count represents the number of orders that have corresponding customer records.

Counting with Group By and Having:

The following statement counts the number of orders per customer with a condition on the count.

SQL Code:


-- Selecting the 'cust_code' column and counting the number of orders for each customer
SELECT cust_code,
       -- Using the COUNT function to count the number of orders for each customer
       COUNT(*) AS order_count
-- From the 'orders' table
FROM orders
-- Grouping the results by 'cust_code' to aggregate order counts per customer
GROUP BY cust_code
-- Filtering the results to include only customers with more than 3 orders
HAVING COUNT(*) > 3;

Explanation:

This SQL query retrieves customer codes (cust_code) and counts the number of orders associated with each customer from the orders table.

It groups the results by cust_code and uses the HAVING clause to filter and include only those customers who have more than 3 orders.

The COUNT(*) function counts the total orders per customer, and the HAVING clause ensures only customers with more than 3 orders are displayed.

The result shows a list of customer codes along with the count of their orders if they exceed 3.

Counting with Subqueries:

The following statement counts rows in a subquery.

SQL Code:


-- Counting the number of rows in the subquery result set
SELECT COUNT(*) 
-- From the subquery result which selects high-value orders
FROM 
(
    -- Selecting all columns from the 'orders' table
    -- where the order amount is greater than 3000
    SELECT * 
    FROM orders 
    WHERE ord_amount > 3000
) 
-- Aliasing the subquery result as 'high_value_orders'
AS high_value_orders;

Explanation:

This SQL query counts the number of high-value orders in the orders table, where the order amount (ord_amount) exceeds 3000.

The inner query (SELECT * FROM orders WHERE ord_amount > 3000) selects all orders with amounts greater than 3000.

The outer query (SELECT COUNT(*)) counts the total number of these high-value orders.

By encapsulating the filter within a subquery and naming it high_value_orders, the query effectively isolates and counts orders meeting the specified condition.

The result is a single value representing the count of orders with amounts over 3000.

SQL COUNT rows in a table

In the following example, an asterisk character ( * ) is used followed by the SQL COUNT() which indicates all the rows of the table even if there is any NULL value.

Sample table: orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

To get number of rows in the 'orders' table, the following SQL statement can be used:

SQL Code:

-- Counting the number of rows in the 'orders' table
SELECT COUNT(*)
-- From the 'orders' table
FROM orders;

Explanation:

SELECT COUNT(*): This part of the query selects the count of all rows in the specified table. The COUNT(*) function is an aggregate function that returns the number of rows in the result set. The * wildcard is used as an argument to COUNT() to indicate that all rows should be counted.

FROM orders: This specifies the table from which to retrieve data. In this case, the table is named 'orders'. The FROM clause is followed by the name of the table or tables from which data is being retrieved.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

 COUNT(*)
---------
       34

Visual Presentation:

Select COUNT(*) from multiple tables

The following query COUNT the number of rows from two different tables (here we use employees and departments) using COUNT(*) command.

SQL Code:

-- Selecting and displaying the count of rows in the 'employees' table as 'Total_Employees'
-- Subquery 1: Counting the number of rows in the 'employees' table
SELECT (
      SELECT COUNT(*)
	  FROM   employees
	  ) AS Total_Employees,
-- Selecting and displaying the count of rows in the 'departments' table as 'No_Of_Departments'
-- Subquery 2: Counting the number of rows in the 'departments' table
	  (SELECT COUNT(*)
	  FROM   departments
	  ) AS No_Of_Departments
-- Using DUAL to create a result set for the combined counts
FROM dual;

Explanation:

SELECT (: Begins the main query to select columns from the result of subqueries.

SELECT COUNT(*) FROM employees: This is a subquery that calculates the total number of records (employees) in the 'employees' table.

AS Total_Employees: Alias assigned to the result of the subquery, naming it 'Total_Employees'.

,: Separates the first subquery from the second subquery.

(SELECT COUNT(*) FROM departments): This is another subquery that calculates the total number of records (departments) in the 'departments' table.

AS No_Of_Departments: Alias assigned to the result of the second subquery, naming it 'No_Of_Departments'.

FROM dual;: 'Dual' is a special one-row, one-column table in Oracle that is used for evaluating expressions. It is used here just to satisfy the SQL syntax requirement as Oracle SQL requires a FROM clause, even though the query does not retrieve any actual data from a table.

Output:

TOTAL_EMPLOYEES NO_OF_DEPARTMENTS
--------------- -----------------
            107                27

SQL COUNT( ) with column name

In this example SQL COUNT() function excludes the NULL values for a specific column if specified the column as an argument in the parenthesis of COUNT function.

Sample table: listofitem

+----------+---------------------------+-------------------------------------+-------------------------------------+
| ITEMCODE | ITEMNAME                  | BATCHCODE                           | CONAME                              |
+----------+---------------------------+-------------------------------------+-------------------------------------+
| I001     | CHOCOLATE                 | DM/2007-08/WBM%1                    |                                     |
| I003     | HOT DOG                   | DM/2007-08/WB1                      | ABJ ENTERPRISE                      |
| I002     | CONDENSED MILK            | DM/2007-08/WBM%2                    | ABJ CONCERN                         |
+----------+---------------------------+-------------------------------------+-------------------------------------+

To get the number of rows in the 'listofitem' table with the following condition -

1. COUNT number of rows for the column 'coname'

the following SQL statement can be used :

SQL Code:


-- Counting the number of non-null values in the 'coname' column of the 'listofitem' table
SELECT COUNT(coname)
-- From the 'listofitem' table
FROM listofitem;

Explanation:

SELECT COUNT(coname): This is the main part of the SQL query. It selects the count of non-null values in the 'coname' column of the 'listofitem' table. The COUNT() function is an aggregate function that counts the number of rows in a result set. When passed a column name like 'coname', it counts the number of non-null values in that column.

FROM listofitem;: This specifies the source of the data for the query, which is the 'listofitem' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'listofitem' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

COUNT(CONAME)
-------------
            2

Explain:

The above statement COUNTs those rows for the 'coname' column which are not NULL.

SQL COUNT rows with user defined column heading

To get number of rows in the 'orders' table with the following condition -

1. result have to display with a heading 'Number of Rows',

the following SQL statement can be used:

SQL Code:


-- Counting the number of rows in the 'orders' table and aliasing the result as "Number of Rows"
SELECT COUNT(*) as "Number of Rows"
-- From the 'orders' table
FROM orders;

Explanation:

SELECT COUNT(*): This is the main part of the SQL query. It uses the COUNT() function to count the number of rows in the 'orders' table. The asterisk (*) is a wildcard that represents all columns in the table. So, COUNT(*) counts the total number of rows in the table, regardless of the values in any particular column.

AS "Number of Rows": This renames the column returned by the COUNT(*) function as "Number of Rows". The AS keyword is used to give an alias or a new name to the result column. In this case, the result will have a single column with the name "Number of Rows".

FROM orders;: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.

Output:

Number of Rows
--------------
            36

SQL COUNT( ) with where clause

The WHERE clause can be used along with SQL COUNT() function to select specific records from a table against a given condition.

Example:

Sample table: orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

To get number of rows in the 'orders' table with following condition -

1. ord_amount against the order is more than 1500,

the following SQL statement can be used :

-- Counting the number of rows in the 'orders' table where 'ord_amount' is greater than 1500
-- and aliasing the result as "Number of Rows"
SELECT COUNT(*) as "Number of Rows"
-- From the 'orders' table
FROM orders
-- Filtering the results to include only rows where 'ord_amount' is greater than 1500
WHERE ord_amount > 1500;

Explanation:

SELECT COUNT(*): This is the main part of the SQL query. It uses the COUNT(*) function to count the number of rows in the 'orders' table. The asterisk (*) is a wildcard that represents all columns in the table. So, COUNT(*) counts the total number of rows in the table.

AS "Number of Rows": This renames the column returned by the COUNT(*) function as "Number of Rows". The AS keyword is used to give an alias or a new name to the result column. In this case, the result will have a single column with the name "Number of Rows".

FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.

WHERE ord_amount > 1500: This is a condition applied to the data being selected. It filters the rows from the 'orders' table where the value in the 'ord_amount' column is greater than 1500. This condition restricts the count to only include rows where the order amount is greater than 1500.

Output:

Number of Rows
--------------
            22

Application of COUNT() function

In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

COUNT with DISTINCT page discusses how to apply COUNT function with DISTINCT and also discusses how to apply COUNT function with ALL clause. Unlike using *, when ALL is used, NULL values are not selected.

COUNT HAVING page discusses how to apply COUNT function with HAVING clause and HAVING and GROUP BY .

COUNT with GROUP BY page discusses how to apply COUNT function with GROUP BY in ascending order and in descending order.

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Frequently Asked Questions (FAQ) - SQL COUNT() Function

1. What does the SQL COUNT() function do?

The COUNT() function in SQL returns the number of rows that match a specified condition. It's widely used to count the total entries in a table, count unique values, or count rows that meet specific criteria.

2. Can COUNT() be used to count only unique values?

Yes, COUNT() can be used with the DISTINCT keyword to count only unique values in a column, excluding duplicates.

3. How does COUNT() handle NULL values?

COUNT(*) includes all rows in its count, even those with NULL values in any column. In contrast, COUNT(column_name) counts only non-NULL values in the specified column.

4. What are the different variations of the COUNT() function?

COUNT() can be used with various options:

COUNT(*): Counts all rows in a table.

COUNT(column_name): Counts non-NULL values in a specified column.

COUNT(DISTINCT expression): Counts unique non-NULL values in a specified expression.

5. Are there any database-specific differences in the COUNT() function usage?

While most databases follow the standard SQL syntax for COUNT(), some like Oracle and DB2 allow using COUNT with window functions, which can provide additional counting capabilities within defined data partitions.

6. How does the OVER clause affect the COUNT() function?

The OVER clause is used with COUNT() to apply window functions, enabling counting over specific data partitions or ordered rows, rather than aggregating all rows into a single result.

7. Is it possible to count rows from multiple tables using COUNT()?

Yes, you can use subqueries or joins to count rows from multiple tables, providing flexibility in analyzing combined datasets.

8. Can COUNT() be used with the GROUP BY and HAVING clauses?

Yes, COUNT() is often used with GROUP BY to count rows within each group of specified column values. The HAVING clause can then filter groups based on the count results.

9. What happens if no rows match the condition specified in COUNT()?

If no rows match the condition, COUNT() returns 0.

10. How is the COUNT() function typically applied in SQL queries?

The COUNT() function is used in various contexts, including counting total rows in a table, counting non-NULL values in a column, counting distinct values, and counting rows based on filtered conditions.

Here is a slide presentation of all aggregate functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Aggregate functions
Next: COUNT with Distinct