COUNT|V4.3.3|OceanBase Database| docs|Distributed Database

COUNT

Last Updated：2024-12-02 03:48:26 Updated

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 OVER clause 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 OVER clause.

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`. `ALL`: returns all values, including duplicate rows. Rows with NULLs are ignored. `DISTINCT`: returns deduplicated rows. Rows with NULLs are ignored. `UNIQUE`: returns deduplicated rows. Rows with NULLs are ignored.
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 returns NULL. If you specify expr, the function returns the number of rows where expr is not NULL. If you specify the asterisk (*), that is, COUNT(*), the function returns the number of all rows. When the DISTINCT, UNIQUE, or ALL parameter is used, separate them from expr with a space.
If you specify the DISTINCT or UNIQUE keyword, you can specify only analytic_clause. order_by_clause and windowing_clause are 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

OceanBase

Customer Stories

Documentation