All Products
Search
Document Center

COUNT

Last Updated: Jun 18, 2021

The COUNT function queries the number of rows for expr.

Syntax

COUNT({ * | [ DISTINCT | UNIQUE | ALL ] expr }) [ OVER (analytic_clause) ]

If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.

Parameters

Parameter

Description

*

All the rows that meet the conditions and include the rows whose values are NULL.

DISTINCT

Removes duplicate rows from returned rows and ignores the rows whose values are NULL.

UNIQUE

Removes duplicate rows from returned rows and ignores the rows whose values are NULL.

ALL

Returns all the values, including duplicate rows, and ignores the rows whose values are NULL.

expr

The expression of the numeric type or the types that can be converted to the numeric type. The numeric type can be NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE.

OVER

Uses the OVER clause to define a window for calculation.

Notice

  • The COUNT function never returns NULL. If you specify expr, the function returns the number of rows where expr is not NULL. If you specify COUNT(*), the function returns the number of all the rows. If you use the DISTINCT, UNIQUE, or ALL parameter, separate the parameter and expr with a space.

  • If you specify the DISTINCT or UNIQUE keyword, order_by_clause and windowing_clause cannot appear in analytic_clause.

Return type

The return type is the same as the data type of the expr parameter.

Examples

The following statements create the employees table and insert data into the table:

CREATE TABLE employees(manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(300, 'Wei',  '2019-09-11',23600);     
INSERT INTO employees VALUES(200, 'Red', '2019-11-05', 23800);
INSERT INTO employees VALUES(100, 'Part',  '2018-10-01',24000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',23500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 23000);
INSERT INTO employees VALUES(200, 'Part',  '2018-06-11',24500);    
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
COMMIT;

Examples of the analytic function

Execute the following statement to query the number of rows in the table:

SELECT last_name, salary,COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING 
AND 150 FOLLOWING) AS mov_count FROM employees ORDER BY salary, last_name;

The following query result is returned:

+-----------+--------+-----------+
| LAST_NAME | SALARY | MOV_COUNT |
+-----------+--------+-----------+
| Errazuriz |   1400 |         1 |
| De Haan   |  11000 |         1 |
| Hartstein |  14000 |         1 |
| Bell      |  23000 |         1 |
| Ross      |  23500 |         2 |
| Wei       |  23600 |         1 |
| Red       |  23800 |         1 |
| Part      |  24000 |         1 |
| Part      |  24500 |         1 |
+-----------+--------+-----------+

Examples of the aggregate function

To create a table named a and insert data into the table, execute the following statements:

CREATE TABLE a (
b INT
);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (null);
INSERT INTO a VALUES (null);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (null);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (1);

To return the number of rows whose values are not NULL in table a, execute the following statement:

SELECT COUNT(b) FROM a;

The following query result is returned:

+----------+
| COUNT(B) |
+----------+
|        4 |
+----------+

To specify COUNT(*) to return the number of all the rows, execute the following statement:

SELECT COUNT(*) FROM a;

The following query result is returned:

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+