COUNT_IF function for counting records based on a condition - MaxCompute

Counts the number of rows for which expr evaluates to TRUE.

COUNT_IF is an aggregate function. For the full list of aggregate functions, see Aggregate functions.

Syntax

bigint count_if(boolean <expr>)

Parameters

Parameter	Required	Type	Description
`expr`	Yes	BOOLEAN	The condition to evaluate. Rows where `expr` is FALSE or NULL are not counted.

Return value

Returns a BIGINT value representing the number of rows where expr evaluates to TRUE.

NULL handling: If expr evaluates to NULL for a row, that row is not counted. When a comparison operand is NULL, the comparison result is NULL rather than TRUE or FALSE, so the row does not satisfy the condition.

Usage notes

If you use new data types — TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY — enable the MaxCompute V2.0 data type edition first:
- Session level: Add set odps.sql.type.system.odps2=true; before your SQL statement and run both together.
- Project level: Run the following statement as the project owner:
```
setproject odps.sql.type.system.odps2=true;
```
  The setting takes effect after 10 to 15 minutes. For details, see Project operations and Data type editions.
If a query contains multiple aggregate functions and project resources are insufficient, memory overflow may occur. To reduce this risk, optimize your SQL statement or purchase additional computing resources.

Examples

Set up sample data

Create an emp table and load the sample data:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path to your data file.

The emp.txt file contains the following data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Count rows matching multiple conditions

Use two COUNT_IF calls in the same query to count rows satisfying two conditions simultaneously:

select count_if(sal > 1000), count_if(sal <= 1000) from emp;

Output:

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 15         | 2          |
+------------+------------+

The first column counts employees with sal > 1000 (15 rows). The second counts those with sal <= 1000 (2 rows). SMITH (sal=800) and JAMES (sal=950) are the only two employees below the threshold.

Verify NULL handling

Rows where the comm column is NULL are not counted, because comparing NULL to any value produces NULL rather than TRUE:

select count_if(comm > 0), count_if(comm is not null) from emp;

Output:

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 3          | 4          |
+------------+------------+

count_if(comm > 0) returns 3: ALLEN (300), WARD (500), and MARTIN (1400) have a positive commission. TURNER has comm=0, which does not satisfy > 0.
count_if(comm is not null) returns 4: ALLEN, WARD, MARTIN, and TURNER all have a non-NULL comm. All other rows have NULL in the comm column and are excluded.

Related functions

For other functions that aggregate values across multiple rows, see Aggregate functions.