Skip to main content

COUNTIF aggregate function

Returns the number of truve values for the group in boolean-expr.

Syntax

COUNTIF( [DISTINCT] boolean-expr )

Function qualifier DISTINCT is only supported on Databricks. Vertica will throw an error if DISTINCT is supplied.

Arguments

  • boolean-expr: A BOOLEAN expression

Returns

A BIGINT

If DISTINCT is supplied, only unique rows are counted. If it is not supplied, all rows will be counted.

Examples

> SELECT countif(x > 0) FROM (SELECT -1 x UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2);
2

-- DATABRICKS ONLY, VERTICA WILL THROW
> SELECT countif(DISTINCT x > 0) FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2);
2