COUNT

COUNT returns the number of rows in a table or window partition. Rows where the specified column or expression evaluates to NULL are excluded from the count.

COUNT is both an aggregate function and a window function.

Syntax

Aggregate function

BIGINT COUNT([DISTINCT | ALL] <colname>)

Window function

BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

Parameters

Parameter	Required	Description
`DISTINCT \| ALL`	No	Controls duplicate handling. `ALL` (default) counts all non-NULL rows. `DISTINCT` counts only unique non-NULL values.
`colname`	Yes	The column to count. Accepts any data type. Use `*` to count all rows, including rows where the column value is NULL.
`expr`	Yes	An expression of any data type. NULL rows are excluded. With `DISTINCT`, only unique non-NULL values are counted. `COUNT([DISTINCT] <expr>[,...]) OVER` counts rows where all specified expressions are non-NULL.
`partition_clause`, `orderby_clause`, `frame_clause`	No	Window definition clauses. See Window functions.

Return value

Returns BIGINT. NULL rows are excluded unless you use COUNT(*).

Usage notes

Window function constraints

Window functions can only appear in SELECT statements.
A window function cannot be nested inside another window function or aggregate function.
Window functions and aggregate functions cannot be used at the same level.

Window frame behavior with ORDER BY

When you use COUNT as a window function with ORDER BY, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This produces a running count from the first row to the current row within each partition.

Without ORDER BY, the window frame spans the entire partition, so every row in the partition returns the same total count.

Hive compatible mode

The ORDER BY behavior differs between Hive compatible and non-Hive compatible modes:

Non-Hive compatible mode (SET odps.sql.hive.compatible=false): Returns the running count from the first row to the current row.
Hive compatible mode (SET odps.sql.hive.compatible=true): Returns the total count of all rows in the partition, regardless of the current row position. This is equivalent to omitting ORDER BY.

Examples

Prepare test data

Skip this step if you already have data to work with.

Download the test_data.txt file.

Create a test table.

CREATE TABLE IF NOT EXISTS emp(
  empno    BIGINT,
  ename    STRING,
  job      STRING,
  mgr      BIGINT,
  hiredate DATETIME,
  sal      BIGINT,
  comm     BIGINT,
  deptno   BIGINT
);

Load data into the table. Replace FILE_PATH with the actual path to your data file.
```
TUNNEL UPLOAD FILE_PATH emp;   
```

Example 1: Partition a window without sorting

Partition the window by sal. Without ORDER BY, each row returns the total count of rows in its partition.

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

Result:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Two rows share sal=1250; both return 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Example 2: Partition a window with sorting (non-Hive compatible mode)

In non-Hive compatible mode, adding ORDER BY produces a running count. Each row returns the cumulative count from the first row to the current row in its partition.

-- Disable Hive compatible mode.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Result:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Running count starts at 1 for the first row in the partition.
| 1250       | 2          |  -- Increments to 2 for the second row.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

Example 3: Partition a window with sorting (Hive compatible mode)

In Hive compatible mode, ORDER BY does not produce a running count. Every row in the partition returns the total partition count, the same as omitting ORDER BY.

-- Enable Hive compatible mode.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Result:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Both rows in the partition return the full partition count.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Example 4: Count all rows in a table

SELECT COUNT(*) FROM emp;

Result:

+------------+
| _c0        |
+------------+
| 17         |
+------------+

Example 5: Count rows per group

Use COUNT with GROUP BY to get the number of employees per department.

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

Example 6: Count unique values

Use DISTINCT to count the number of distinct departments.

SELECT COUNT(DISTINCT deptno) FROM emp;

Result:

+------------+
| _c0        |
+------------+
| 3          |
+------------+

Related functions

For other aggregate functions, see Aggregate functions.
For other window functions, see Window functions.