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
COUNTfunction never returnsNULL. If you specifyexpr, the function returns the number of rows whereexpris notNULL. If you specifyCOUNT(*), the function returns the number of all the rows. If you use theDISTINCT,UNIQUE, orALLparameter, separate the parameter andexprwith a space.If you specify the
DISTINCTorUNIQUEkeyword,order_by_clauseandwindowing_clausecannot appear inanalytic_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 |
+----------+