Categories:

Aggregate Functions (General) , Window Functions (General, Window Frame)

COUNT_IF¶

Returns the number of records that satisfy a condition or NULL if no records satisfy the condition.

See also:

COUNT

Syntax¶

Aggregate function

COUNT_IF( <condition> )
Copy

Window function

COUNT_IF( <condition> )
    OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] ] )
Copy

For details about window_frame syntax, see Window Frame Syntax and Usage.

Arguments¶

condition

The condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL)

expr1

The column to partition on, if you want the result to be split into multiple windows.

expr2

The column to order each window on. Note that this is separate from any ORDER BY clause to order the final result set.

Returns¶

If the function does not return NULL, the data type of the returned value is NUMBER.

Usage Notes¶

  • When this function is called as a window function:

    • If an ORDER BY sub-clause is used inside the OVER() clause, then a window frame must be used. If no window frame is specified, then the default is a cumulative window frame:

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

      For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage. For more information about implied window frames, see Window Frame Usage Notes.

Examples¶

The examples in this section demonstrate how to use the COUNT_IF function.

The following statements set up a table for use in the examples:

CREATE TABLE basic_example (i_col INTEGER, j_col INTEGER);
INSERT INTO basic_example VALUES
    (11,101), (11,102), (11,NULL), (12,101), (NULL,101), (NULL,102);
Copy
SELECT *
    FROM basic_example
    ORDER BY i_col;
Copy
+-------+-------+
| I_COL | J_COL |
|-------+-------|
|    11 |   101 |
|    11 |   102 |
|    11 |  NULL |
|    12 |   101 |
|  NULL |   101 |
|  NULL |   102 |
+-------+-------+

The following example passes in TRUE for the condition, which returns the count of all rows in the table:

SELECT COUNT_IF(TRUE) FROM basic_example;
Copy
+----------------+
| COUNT_IF(TRUE) |
|----------------|
|              6 |
+----------------+

The following example returns the number of rows where the value in J_COL is greater than the value in I_COL:

SELECT COUNT_IF(j_col > i_col) FROM basic_example;
Copy
+-------------------------+
| COUNT_IF(J_COL > I_COL) |
|-------------------------|
|                       3 |
+-------------------------+

Note that in the example above, the count does not include rows with NULL values. As explained in Ternary Logic, when any operand for a comparison operator is NULL, the result is NULL, which does not satisfy the condition specified by COUNT_IF.

The following example returns the number of rows that do not contain any NULL values.

SELECT COUNT_IF(i_col IS NOT NULL AND j_col IS NOT NULL) FROM basic_example;
Copy
+---------------------------------------------------+
| COUNT_IF(I_COL IS NOT NULL AND J_COL IS NOT NULL) |
|---------------------------------------------------|
|                                                 3 |
+---------------------------------------------------+