count

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Counting. Counts rows. count(*) counts every row of the result; count(x) counts the rows where x isn't NULL. count() and count_star() are the same as count(*).

Syntax

count(*)
count()
count_star()
count(x)
count(ALL x)

Returns: BIGINT.

Aliases: count() and count_star() are the same as count(*). count(ALL x) is the same as count(x).

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. 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 3 has no temperature, reading 2 has an empty list of tags, 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"'

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, tags VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 's1', 18.5, ['indoor']), "
    "(2, 's1', 21.0, []), "
    "(3, 's2', NULL, ['outdoor']), "
    "(4, 's2', 24.0, ['outdoor', 'roof']), "
    "(5, NULL, NULL, NULL)")

[]
[]
[(5,)]

What it does

count is an aggregate: it turns the rows of a query into one number.

• count(*) counts every row that reaches it, including rows where every column is NULL. count() and count_star() are other spellings of it; the plan shows count(*) as count_star().
• count(x) counts the rows where x isn't NULL. x can be of any type: numbers, text, BLOB, dates and times, BOOLEAN, lists. An empty string or an empty list isn't NULL, so it's counted.
• count(expression) counts the rows where the expression isn't NULL, not the rows where it's true: count(temperature > 20) also counts the readings at 20 degrees or below. To count the rows where a condition holds, use count_if or count(*) FILTER (WHERE ...).

An aggregate query returns one row for the whole result: there's no GROUP BY (see Limitations and errors).

NULLs and edge cases

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

run("SELECT count(*), count(42), count(NULL)")
run(f"SELECT count(*), count(), count_star(), count(temperature), count(tags), count(NULL) FROM {TABLE}")
run(f"SELECT count(*), count(temperature) FROM {TABLE} WHERE id > 100")
run(f"SELECT count(temperature > 20), count_if(temperature > 20) FROM {TABLE}")
run(f"SELECT count(DISTINCT sensor) FROM {TABLE}")
run(f"SELECT count(id, sensor) FROM {TABLE}")
run(f"SELECT count(*, id) FROM {TABLE}")
run(f"SELECT count_star(id) FROM {TABLE}")

[(1, 1, 0)]
[(5, 5, 5, 3, 4, 0)]
[(0, 0)]
[(3, Decimal('2'))]
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: Binder Error: No function matches the given name and argument types 'count(BIGINT, VARCHAR)'. You might need to add explicit type casts.
InternalError: Binder Error: Function "count" cannot be applied to a star expression
InternalError: Binder Error: No function matches the given name and argument types 'count_star(BIGINT)'. You might need to add explicit type casts.

Examples

The number of readings, the number with a temperature, and the number with a sensor name:

run(f"SELECT count(*), count(temperature), count(sensor) FROM {TABLE}")

[(5, 3, 4)]

Count different subsets in one query with FILTER, and the readings above 20 degrees with a WHERE condition:

run(f"SELECT count(*) FILTER (WHERE temperature > 20) AS warm, "
    f"count(*) FILTER (WHERE temperature IS NULL) AS missing FROM {TABLE}")
run(f"SELECT count(*) FROM {TABLE} WHERE temperature > 20")

[(2, 2)]
[(2,)]

Use the count in an expression or a HAVING condition. Without GROUP BY, HAVING keeps or drops the one result row:

run(f"SELECT CASE WHEN count(*) > 3 THEN 'many' ELSE 'few' END FROM {TABLE}")
run(f"SELECT count(*) FROM {TABLE} HAVING count(*) > 100")

[('many',)]
[]

How it runs in VAST

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

1. A scan in VAST DataBase (VAST_SCAN). It reads only the columns the query needs, and evaluates the WHERE conditions it can push down, so rows that don't match never leave storage. For count(*) it reads no column at all: the plan has no Projections line.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine, which counts the rows the scan sends and returns one row.

The plans of count(*) and count(temperature), and of count(*) with a WHERE condition that's pushed down. The condition doesn't appear in the plan, because the scan evaluates it:

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

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

Make it faster

• Count with count(*) when you can. It reads no column; count(x) reads x to find its NULLs.
• Filter in WHERE, not in FILTER (WHERE ...). A WHERE condition that compares a column with a constant is evaluated in VAST DataBase, so fewer rows reach the Query Engine. A FILTER condition is evaluated in the aggregate step, after the scan has sent every row: its plan shows the condition inside UNGROUPED_AGGREGATE. Use FILTER when you need different conditions for different counts in one query.
• Keep functions out of the WHERE condition. A function of a column, such as abs(temperature) > 20, isn't pushed down: the plan shows a FILTER step in the Query Engine, and the scan sends every row.
• Compute several aggregates in one query. They share one scan and one aggregate step.

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│ count_star() FILTER (WHERE│
│    (temperature > 20.0))  │
│  Filter: (temperature > 20│
│            .0)            │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (abs(temperature) > 20.0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
└───────────────────────────┘

Limitations and errors

• No GROUP BY. A count per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported., and so does SELECT DISTINCT. Run one query per group with a WHERE condition, use FILTER for a few known groups, or run the query in Trino or Spark.
• No count(DISTINCT x). It fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Use approx_count_distinct for an estimate.
• A column next to an aggregate. SELECT id, count(*) fails with column "id" must appear in the GROUP BY clause or must be part of an aggregate function. Select only aggregates.
• No count in a subquery expression. SELECT (SELECT count(*) FROM t) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Run the count as its own query; a count over a derived table, SELECT count(*) FROM (SELECT ...), works.
• Window forms. count(*) OVER (ROWS UNBOUNDED PRECEDING) runs and gives a running count in the order the scan returns the rows, which you can't choose. 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 sensor, count(*) FROM {TABLE} GROUP BY sensor")
run(f"SELECT id, count(*) FROM {TABLE}")
run(f"SELECT (SELECT count(*) FROM {TABLE})")
run(f"SELECT count(*) FROM (SELECT * FROM {TABLE} WHERE temperature > 20)")
run(f"SELECT id, count(*) OVER () FROM {TABLE}")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Binder Error: column "id" must appear in the GROUP BY clause or must be part of an aggregate function.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
[(2,)]
InternalError: The operator 'WINDOW' type=6 is currently not supported.

Similar functions

The three ways to count the rows where a condition holds give the same count; they differ on no rows:

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

[(Decimal('2'), 2, Decimal('2'), 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_if, approx_count_distinct and FILTER.
• SELECT, pushdowns and EXPLAIN.