COUNT_IF function in snowflake

Explained: COUNT_IF() function in Snowflake?

by

Are you looking to find how to use the COUNT_IF() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the COUNT_IF function in Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what is it and how to use the COUNT_IF() function. I will explain the COUNT_IF() function by taking a practical example. So don’t waste time, let’s start a step by step guide to understanding the COUNT_IF() expression.

What is COUNT_IF() Function ?

COUNT_IF() function helps in providing the number of records that satisfies the required condition.

How can we use COUNT_IF() Function ?

The COUNT_IF() function can be used as an aggregate function or as a Windows function. 

Note: If an ORDER BY sub-clause is specified inside the OVER() clause, then a window frame must be used. The OVER clause specifies that the function is being used as a window function. Syntax is mentioned below for the same.

Types of Window Frames

Snowflake supports two types of window frames:

Cumulative

It enables computing rolling values from the beginning of the window to the current row or from the current row to the end of the window.

Sliding

Enables computing rolling values between any two rows (inclusive) in the window, relative to the current row.

What is the Syntax of the COUNT_IF function?

-- Syntax :
Aggregate Function:
COUNT_IF( <condition> )
Windows Function:
COUNT_IF( <condition> )
OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] ] )

COUNT_IF() Argument Details :

ParameterParameter Description
ConditionIt is defined as the Boolean expression
Expr1It is defined as Partition By Sub-clause
Expr2It is defined as Order By Sub-clause
Table1: Parameter Table for Count_If() in Snowflake

Condition:

The condition helps in evaluating the BOOLEAN value i.e. TRUE, FALSE or NULL.

Expr1:

Expr1 can be used when you want the result to be split into multiple windows. This function basically helps in grouping the data. For example: by state, by country, by month, etc.

Expr2:

The column to order each window on. The ORDER BY clause orders rows within the window. (This is different from ordering the output of a query. A query might have one ORDER BY clause that controls the order of rows within a window, and a separate ORDER BY clause, outside the OVER clause, that controls the output order of the entire query.

Examples of COUNT_IF() function in Snowflake:

Firstly, Let’s take sample data to test the COUNT_IF function.

create table test3(k_col integer, i_col integer);
insert into test3 values
                     (201,21),
                     (202,22),
                     (null,21),
                     (201,22),
                     (201,null),
                     (202,null),
                     (203,23);

select * from test3 order by i_col;
+-------+-------+
| K_COL | I_COL |
|-------+-------|
|   201 |    21 |
|  NULL |    21 |
|   202 |    22 |
|   201 |    22 |
|   203 |    23 |
|   201 |  NULL |
|   202 |  NULL |
+-------+-------+

select count_if(true) from test3;
+----------------+
| COUNT_IF(TRUE) |
|----------------|
|              7 |
+----------------+

As we can see in the above example, TRUE condition will return the count of all rows in the table.

select count_if(i_col<k_col) from test3;
+-----------------------+
| COUNT_IF(I_COL<K_COL) |
|-----------------------|
|                     4 |
+-----------------------+

As we can see in the above example, it returns only the values which satisfies the condition of K_COL greater than I_COL.

When you should use COUNT_IF() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the COUNT_IF function within the Snowflake cloud data warehouse which are as follows: 

We can use this function whenever a count of records is required for comparing two entities. For example: You can find in how many subjects Sujeet has scored more marks than Naveen.

Limitations of COUNT_IF Function

  • COUNT_IF will not provide the exact values of records for which the condition is true.
  • COUNT_IF will only provide the result count for true condition.

Real World Use Case Scenarios for COUNT_IF Function in Snowflake

  • Let’s consider Rahul and Vineet are purchasing the same 6 products from 2 different stores. We can find which store sells how many products at a higher cost or how many products have been sold at the same price.

COUNT_IF() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about the COUNT_IF function and use it with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Prateek Gupta