Purpose
This function returns the number of rows that match the query condition. It can be used as an aggregate function or an analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for calculation. It calculates over a set of rows and returns multiple values. - When used as an aggregate function, this function aggregates a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
COUNT({ * | [ DISTINCT | UNIQUE | ALL ] expr }) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| * | Indicates to return all rows that meet the condition, including duplicate rows and rows with NULL values. |
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates. This is an optional parameter with a default value of ALL.
|
| expr | Specifies the name of the column to be calculated. |
| OVER | Defines the window for calculation using the OVER clause. For more information, see Analytic Function Description. |
Notice
- The
COUNTfunction never returnsNULL. If you specifyexpr, it returns the count of rows whereexpris notNULL. If you specifyCOUNT(*), it returns the total number of rows. When using theDISTINCT,UNIQUE, orALLparameters, they must be separated fromexprby a space. - If you specify the
DISTINCTorUNIQUEkeyword, theorder_by_clauseandwindowing_clausecannot appear in theanalytic_clause.
Return type
A natural number.
Examples
Create a table named tbl1 and insert test data.
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
Aggregate function example
Count the number of non-NULL rows in column col3 of table tbl1 and the total number of rows in table tbl1.
obclient> SELECT COUNT(col3),COUNT(*) FROM tbl1;
+-------------+----------+
| COUNT(COL3) | COUNT(*) |
+-------------+----------+
| 10 | 12 |
+-------------+----------+
1 row in set
Analytic function example
Count the number of rows in table tbl1 where the value of col3 is between 1 and 3.
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