Skip to main content

COUNTIF aggregate function

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

Syntax

COUNTIF( [DISTINCT] boolean-expr )

The function qualifier DISTINCT is only supported in Spark-based engine. Vertica will return 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

-- Standard usage supported across all environments
SELECT countif(x > 0) FROM (SELECT -1 x UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2);
-- Result: 2

-- Extended syntax (available in Spark-based engine only)
-- Note: This syntax is not supported in Vertica
SELECT countif(DISTINCT x > 0) FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2);
-- Result: 2