count_if

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Counting. Counts the rows where a condition is true. countif is the same function.

Syntax

count_if(condition)
countif(condition)

Returns: HUGEINT, a 128-bit integer. ADBC returns it as DECIMAL(38, 0), which Python prints as Decimal.

Aliases: countif.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_aggregate_count_if. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of five sensor readings. Reading 4 has no ok flag, and reading 5 has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_count_if"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sensor VARCHAR, temperature DOUBLE, ok BOOLEAN)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 's1', 18.5, true), "
    "(2, 's1', 21.0, true), "
    "(3, 's2', 19.5, false), "
    "(4, 's2', 24.0, NULL), "
    "(5, NULL, NULL, NULL)")

[]
[]
[(5,)]

What it does

count_if(condition) is an aggregate: it evaluates the condition on each row and counts the rows where it's true. Rows where it's false or NULL aren't counted.

The condition must be a BOOLEAN: a comparison (temperature > 20), a test (sensor IS NULL, sensor LIKE 's%'), a BOOLEAN column, or NOT of one. A number isn't taken as true or false: count_if(1) is an error.

On no rows, count_if returns NULL, not 0. It also returns NULL when the condition is NULL on every row. Wrap it in coalesce to get 0, or use count(*) FILTER (WHERE condition), which returns 0 (see Similar functions).

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT count_if(ok), count_if(NOT ok), count_if(ok IS NULL), count_if(false) FROM {TABLE}")
run(f"SELECT count_if(ok) FROM {TABLE} WHERE id = 5")
run(f"SELECT count_if(ok), coalesce(count_if(ok), 0) FROM {TABLE} WHERE id > 100")
run("SELECT count_if(true), count_if(NULL)")
run(f"SELECT count_if(1) FROM {TABLE}")
run(f"SELECT count_if('true') FROM {TABLE}")
run(f"SELECT count_if(DISTINCT ok) FROM {TABLE}")

[(Decimal('2'), Decimal('1'), Decimal('2'), Decimal('0'))]
[(None,)]
[(None, Decimal('0'))]
[(Decimal('1'), None)]
InternalError: Binder Error: No function matches the given name and argument types 'count_if(INTEGER)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'count_if(VARCHAR)'. You might need to add explicit type casts.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Examples

Several counts over the same rows in one query: readings above 20 degrees, readings flagged as not OK, and readings with no sensor name. countif gives the same result as count_if:

run(f"SELECT count_if(temperature > 20), countif(temperature > 20), count_if(NOT ok), "
    f"count_if(sensor IS NULL) FROM {TABLE}")

[(Decimal('2'), Decimal('2'), Decimal('1'), Decimal('1'))]

The share of readings above 20 degrees. / returns a DOUBLE, so the result is a fraction:

run(f"SELECT count_if(temperature > 20) / count(*) FROM {TABLE}")

[(0.4,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. A count_if query has two steps, which EXPLAIN shows:

1. A scan in VAST DataBase (VAST_SCAN), which reads the columns the condition uses and evaluates the WHERE conditions it can push down.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine. It evaluates the condition of count_if on every row the scan sends, and counts the rows where it's true.

The condition inside count_if isn't pushed down: the scan sends every row, and the plan shows the condition inside the aggregate step, with no FILTER step:

plan(f"SELECT count_if(temperature > 20) FROM {TABLE}")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│ count_if((temperature > 20│
│            .0))           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
└───────────────────────────┘

Make it faster

• For one count, put the condition in WHERE. SELECT count(*) FROM t WHERE temperature > 20 gives the same count (as a BIGINT, and 0 on no rows), and its condition is evaluated in VAST DataBase, so the rows that don't match never leave storage. The plan has no condition at all:

plan(f"SELECT count(*) FROM {TABLE} WHERE temperature > 20")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
└───────────────────────────┘

• For several counts, use one query. Several count_if calls share one scan and one aggregate step; separate queries scan the table again each time.
• Combine it with a pushed-down condition. In SELECT count_if(ok) FROM t WHERE id > 100, id > 100 is still evaluated in VAST DataBase, so count_if sees only the rows that pass it.

Limitations and errors

• BOOLEAN only. A number, a string, a date or a list fails with No function matches the given name and argument types; the error lists the one form that exists, count_if(BOOLEAN) -> HUGEINT. Write a comparison: count_if(n <> 0).
• NULL on no rows. See What it does.
• No GROUP BY or DISTINCT. A count per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported.; count_if(DISTINCT ...) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• Window forms. count_if(c) OVER (ROWS UNBOUNDED PRECEDING) runs, as a running count in the order the scan returns the rows. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.

run(f"SELECT count_if(id) FROM {TABLE}")
run(f"SELECT count_if(id <> 0) FROM {TABLE}")
run(f"SELECT sensor, count_if(ok) FROM {TABLE} GROUP BY sensor")

InternalError: Binder Error: No function matches the given name and argument types 'count_if(BIGINT)'. You might need to add explicit type casts.
[(Decimal('5'),)]
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Similar functions

The same count four ways, then on no rows. The last column shows that count(condition) counts the false rows too:

run(f"SELECT count_if(temperature > 20), count(*) FILTER (WHERE temperature > 20), "
    f"sum(CASE WHEN temperature > 20 THEN 1 ELSE 0 END), count(temperature > 20) FROM {TABLE}")
run(f"SELECT count(*) FROM {TABLE} WHERE temperature > 20")
run(f"SELECT count_if(temperature > 20), count(*) FILTER (WHERE temperature > 20), "
    f"sum(CASE WHEN temperature > 20 THEN 1 ELSE 0 END), count(temperature > 20) FROM {TABLE} WHERE id > 100")

[(Decimal('2'), 2, Decimal('2'), 4)]
[(2,)]
[(None, 0, None, 0)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Aggregate and analytic functions: all the aggregates, and the ones that aren't available.
• count, FILTER and coalesce.
• Pushdowns and EXPLAIN.