Snowflake COUNT_IF: Count Rows Based on Conditions
COUNT_IF evaluates a given condition for each row in a set of rows and counts the number of times the condition is true. This function can be used both as a standard aggregate function and within a window (over a partition of data).
Syntax
_10-- As an aggregate function_10COUNT_IF( <condition> )_10_10-- As a window function_10COUNT_IF( <condition> ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] ] )
where:
condition: A Boolean expression that returns true or false. Rows that meet this condition are counted.PARTITION BY expr1: The expression to partition the data before applying the count.ORDER BY expr2: The expression to order data within each partition.
Usage Notes
- If using as a window function and you include an
ORDER BYinside theOVER()clause, you must define a window frame. If none is specified, the default is:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. NULLvalues in your data can affect the count if they make the condition resolve toNULL.
Common Use Cases
- Counting the number of entries that exceed a certain value.
- Determining how many rows have non-null values in specific columns.
- Analyzing patterns within subsets of data, for example, counting occurrences of a condition within monthly sales data.
Examples
Let’s consider a table of items with their categories and prices. We want to count how many items in each category are priced above a certain threshold.
Conditional Categorization
Input
Table: item_sales
| category | price |
|---|---|
| Electronics | 300.00 |
| Electronics | 150.00 |
| Books | 23.90 |
| Books | 45.00 |
| Clothing | 75.50 |
| Clothing | 60.00 |
Snowflake SQL Query
We’ll write a query using the IFF function to classify each planet’s safety for visitors:
_10SELECT_10 category,_10 COUNT_IF(price > 50) AS items_above_50_10FROM item_sales_10GROUP BY category;
This query groups the items by their categories and counts how many items in each category are priced above $50.
Output
| category | items_above_50 |
|---|---|
| Electronics | 2 |
| Books | 0 |
| Clothing | 2 |
The COUNT_IF function makes it easy to perform conditional counts directly in SQL without needing to filter the data in subqueries or use case statements within traditional COUNT() functions. This leads to cleaner, more readable SQL and can improve performance for large datasets by reducing the amount of data that needs to be processed.
