--- title: "count" slug: "dev-database-reference-sql-functions-aggregate-count" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-count" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # count *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › 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) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | any type | The value to test. Rows where it's NULL aren't counted. | **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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-count#limitations-and-errors)). ## NULLs and edge cases | Input | Result | | --- | --- | | `count(*)`, `count()`, `count_star()` | Every row, NULLs included | | `count(x)` | The rows where `x` isn't NULL | | `count(x)` where `x` is `''` or `[]` | Counted: they aren't NULL | | `count(NULL)` | `0` | | No rows (an empty table, or a `WHERE` that matches nothing) | `0`, for `count(*)` and `count(x)` | | `count(*)` or `count(42)` with no `FROM` | `1` | | `count(x > 0)` | The rows where `x > 0` isn't NULL: false is counted | | `count(DISTINCT x)` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | | `count(x, y)` | Error: `No function matches the given name and argument types`, for example `'count(BIGINT, VARCHAR)'` | | `count(*, x)` | Error: `Function "count" cannot be applied to a star expression` | | `count_star(x)` | Error: `No function matches the given name and argument types 'count_star(BIGINT)'` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-aggregate-count#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](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause), 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](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-database-reference-pushdowns), 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](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-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 | Function | Counts | Returns | On no rows | | --- | --- | --- | --- | | `count(*)` | every row | `BIGINT` | `0` | | `count(x)` | rows where `x` isn't NULL | `BIGINT` | `0` | | [count_if](/develop/docs/dev-database-reference-sql-functions-aggregate-count-if) `(condition)` | rows where the condition is true | `HUGEINT` | NULL | | `count(*) FILTER (WHERE condition)` ([FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause)) | rows where the condition is true | `BIGINT` | `0` | | `sum(CASE WHEN condition THEN 1 ELSE 0 END)` ([sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum)) | rows where the condition is true | `HUGEINT` | NULL | | [approx_count_distinct](/develop/docs/dev-database-reference-sql-functions-aggregate-approx-count-distinct) `(x)` | an estimate of the distinct non-NULL values | `BIGINT` | `0` | 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](/develop/docs/dev-database-reference-sql-functions-aggregate): all the aggregates, and the ones that aren't available. - [count_if](/develop/docs/dev-database-reference-sql-functions-aggregate-count-if), [approx_count_distinct](/develop/docs/dev-database-reference-sql-functions-aggregate-approx-count-distinct) and [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause). - [SELECT](/develop/docs/dev-database-reference-sql-select#aggregates), [pushdowns](/develop/docs/dev-database-reference-pushdowns) and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain).