# `count_if` aggregate function

Applies to: Databricks SQL Databricks Runtime

Returns the number of true values for the group in `expr`.

## Syntax

```count_if ( [ALL | DISTINCT] expr ) [FILTER ( WHERE cond ) ]
```

This function can also be invoked as a window function using the `OVER` clause.

## Arguments

• `expr`: A BOOLEAN expression.

• `cond`: An optional boolean expression filtering the rows used for aggregation.

## Returns

A BIGINT.

`count_if(expr) FILTER(WHERE cond)` is equivalent to `count_if(expr AND cond)`.

If `DISTINCT` is specified only unique rows are counted.

## Examples

```> SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (2), (3) AS tab(col);
3

> SELECT count_if(DISTINCT col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (2), (3) AS tab(col);
2

> SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
1
```
{"ft_lang_label":"__label__en","ft_lang_prob":0.6262639,"math_prob":0.95563084,"size":540,"snap":"2023-40-2023-50","text_gpt3_token_len":124,"char_repetition_ratio":0.1119403,"word_repetition_ratio":0.0,"special_character_ratio":0.20925926,"punctuation_ratio":0.10869565,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9674983,"pos_list":[0,1,2,3,4],"im_url_duplicate_count":[null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-09-29T20:25:05Z\",\"WARC-Record-ID\":\"<urn:uuid:7a4c70fe-b0d7-4d8b-9cf5-5456045a3a8f>\",\"Content-Length\":\"53131\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:1e81c1e7-02e6-426f-ae21-75487b40505c>\",\"WARC-Concurrent-To\":\"<urn:uuid:00a09afa-21f6-4041-8f3e-e9bc95518da6>\",\"WARC-IP-Address\":\"99.84.191.11\",\"WARC-Target-URI\":\"https://docs.databricks.com/en/sql/language-manual/functions/count_if.html\",\"WARC-Payload-Digest\":\"sha1:ZXVKMSDYVAE4GSHDFSJ5STFMQSEBHIBN\",\"WARC-Block-Digest\":\"sha1:VNBDO5UVVHBB5GC66HTR4ZC7W2XNGOPK\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-40/CC-MAIN-2023-40_segments_1695233510528.86_warc_CC-MAIN-20230929190403-20230929220403-00742.warc.gz\"}"}