SQL COUNT() function
COUNT() function
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. 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 |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
DB2 and Oracle Syntax:
COUNT ({*|[DISTINCT] expression}) OVER (window_clause)
Parameters:
| Name | Description |
|---|---|
| ALL | Applies to all values. ALL returns the number of non NULL values. |
| DISTINCT | Ignored duplicate values and COUNT returns the number of unique nonnull values. |
| 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. |
| * | COUNTs all the rows in the target table whether or not they include NULLs. |
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. See the following examples:
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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics