Purpose
COUNT() returns the number of rows of expr. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
COUNT({ * | [ DISTINCT | UNIQUE | ALL ] expr }) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| * | Specifies to return all rows that meet the condition, including duplicate and NULL rows. |
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates during the execution of the query. This parameter is optional. Default value: ALL.
|
| expr | The name of the column to be calculated. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Notice
- The
COUNT()function never returnsNULL. If you specifyexpr, the function returns the number of rows whereexpris notNULL. If you specify the asterisk (*), that is,COUNT(*), the function returns the number of all rows. When theDISTINCT,UNIQUE, orALLparameter is used, separate them fromexprwith a space. - If you specify the
DISTINCTorUNIQUEkeyword, you can specify onlyanalytic_clause.order_by_clauseandwindowing_clauseare not allowed.
Return type
This function returns a natural number.
Examples
Create the tbl1 table and insert test data into the table.
obclient> CREATE TABLE tbl1 (col1 INT,col2 VARCHAR(10),col3 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES
(1,'aa',10),(2,'bb',12),(3,'cc',15),(4,'dd',18),(5,'ee',20),
(6,'ff',23),(7,'gg',25),(8,'hh',30),(9,'ii',40),(10,'jj',15),
(11,'kk',NULL),(12,'oo',NULL);
Query OK, 12 rows affected
Records: 12 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | aa | 10 |
| 2 | bb | 12 |
| 3 | cc | 15 |
| 4 | dd | 18 |
| 5 | ee | 20 |
| 6 | ff | 23 |
| 7 | gg | 25 |
| 8 | hh | 30 |
| 9 | ii | 40 |
| 10 | jj | 15 |
| 11 | kk | NULL |
| 12 | oo | NULL |
+------+------+------+
12 rows in set
Example of an aggregate function
Obtain the number of non-NULL rows in the col3 column in the tbl1 table and the total number of rows in tbl1.
obclient> SELECT COUNT(col3),COUNT(*) FROM tbl1;
+-------------+----------+
| COUNT(COL3) | COUNT(*) |
+-------------+----------+
| 10 | 12 |
+-------------+----------+
1 row in set
Example of an analytic function
Return the number of values with an offset within the range between 1 and 3 in the ordered col3 column in table tbl1.
obclient> SELECT col1,col2,
COUNT(*) OVER (ORDER BY col3 RANGE BETWEEN 1 PRECEDING AND 3 FOLLOWING) AS mov_count
FROM tbl1;
+------+------+-----------+
| COL1 | COL2 | MOV_COUNT |
+------+------+-----------+
| 1 | aa | 2 |
| 2 | bb | 3 |
| 3 | cc | 3 |
| 10 | jj | 3 |
| 4 | dd | 2 |
| 5 | ee | 2 |
| 6 | ff | 2 |
| 7 | gg | 1 |
| 8 | hh | 1 |
| 9 | ii | 1 |
| 11 | kk | 2 |
| 12 | oo | 2 |
+------+------+-----------+
12 rows in set
