Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/count_if below:

Website Navigation

• Reference
• Function and stored procedure reference
• Aggregate

COUNT_IF | Snowflake Documentation

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

See also:

COUNT

Aggregate function

Window function

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

Copy

For detailed window_frame syntax, see Window function syntax and usage.

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 the ORDER BY clause that sorts the final result set.

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

• When this function is called as a window function with an ORDER BY clause, you must specify a window frame. If you do not specify a window frame, the following default frame is used:

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  For more information about window frames, including syntax, usage notes, and examples, see Window function syntax and usage.

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 |
+---------------------------------------------------+

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4