> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# COUNT_IF

> Counts rows whose condition is true.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

**Aliases:** [`countif`](/query/sql/functions/aggregate/countif)

FALSE and NULL conditions are ignored. Empty input or no true conditions returns 0.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
count_if(condition)
```

### Window within each group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
count_if(condition) OVER (PARTITION BY key)
```

### Frame (window)

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
count_if(condition) OVER (ORDER BY key NULLS LAST ROWS BETWEEN lower_bound AND upper_bound)
```

* For ordered frames with no numeric offsets, rows with the same ORDER BY value are treated together. Use unique ORDER BY values when a running result must advance one row at a time.

## Arguments

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| condition | BOOLEAN | Yes | Condition evaluated for each row. Only TRUE contributes to the count. |

## Returns

* Type: BIGINT
* Possible values: A nonnegative count of TRUE conditions, represented as a signed 64-bit integer.
* NULL behavior: FALSE and NULL conditions are ignored. Empty input or no true conditions returns 0.
* Empty input: 0, including an empty window frame.

## Properties

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports DISTINCT | No |
| Supports window usage | Yes |
| Supports window frames | Partial (Use the ROWS forms shown here; RANGE offsets are not supported.) |
| Exact result | Yes |

## Usage notes

* Use an explicit frame when the calculation depends on which rows belong to the window. The examples specify ROWS frames and a unique row ID.

## Examples

### Sample data

| k | id | flag |
| - | - | - |
| 1 | 1 | true |
| 1 | 2 | false |
| 1 | 3 | NULL |
| 2 | 4 | true |
| 2 | 5 | true |
| 3 | 6 | NULL |

### Basic aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT count_if(flag) AS result FROM sample;
```

| result |
| - |
| 3 |

### Grouped aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT k, count_if(flag) AS result FROM sample GROUP BY k ORDER BY k NULLS LAST;
```

| k | result |
| - | - |
| 1 | 1 |
| 2 | 2 |
| 3 | 0 |

### Window within each group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, count_if(flag) OVER (PARTITION BY k) AS result FROM sample ORDER BY id NULLS LAST;
```

| id | result |
| - | - |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 0 |

### Running frame

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, count_if(flag) OVER (ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result FROM sample ORDER BY id NULLS LAST;
```

| id | result |
| - | - |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |

### Sliding frame

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, count_if(flag) OVER (ORDER BY id NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result FROM sample ORDER BY id NULLS LAST;
```

| id | result |
| - | - |
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |
| 5 | 2 |
| 6 | 1 |

### All-NULL group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT count_if(flag) AS result FROM sample WHERE k = 3;
```

| result |
| - |
| 0 |

### Empty input

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT count_if(flag) AS result FROM sample WHERE FALSE;
```

| result |
| - |
| 0 |

## Limitations

* COUNT\_IF and COUNTIF do not accept DISTINCT.
* RANGE frames are supported only when both ends are unbounded, covering the entire group. Use an explicit ROWS frame for ordered calculations.
* For ordered frames with no numeric offsets, rows with the same ORDER BY value are treated together. Use unique ORDER BY values when a running result must advance one row at a time.

## Related functions

* [count](/query/sql/functions/aggregate/count)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.