ClickHouse GROUP BY and Aggregations in TypeScript

Almost every analytics query you run against ClickHouse is a GROUP BY. Revenue by region, events by day, p95 latency by endpoint — the shape is always the same: pick dimensions, aggregate measures, group, sort.

Short answer: with hypequery you chain typed aggregation methods (.sum(), .avg(), .count(), .quantile(), and friends) and then call .groupBy() explicitly with your dimension columns — exactly as you would write the SQL:

Which produces:

One habit worth keeping: call .groupBy() yourself, with every non-aggregated column you select — the same discipline SQL asks of you. It keeps the mapping between your TypeScript and the generated SQL obvious, and every example in this post follows it.

The rest of this post walks through every aggregation the builder supports natively, plus the three GROUP BY companions that matter most in ClickHouse: time bucketing, WITH TOTALS, and HAVING. If you want to run these against your own schema first, the quick start gets you from npm install to typed queries in a few minutes, and the ClickHouse query builder page covers the builder end to end.

The working schema

Examples below use two tables from a multi-tenant SaaS:

Running hypequery generate against a database containing these tables produces the IntrospectedSchema type used above, so column names and comparison values are checked at compile time.

Counting: count, countDistinct, and the uniq question

.count(column, alias) maps to ClickHouse count():

Two ClickHouse behaviors to keep in mind:

• count() with no argument counts all rows; count(col) skips rows where col is NULL. If a count looks mysteriously low, check whether you're counting a Nullable column.
• .countDistinct(column, alias) generates count(DISTINCT col), which ClickHouse executes as uniqExact by default — exact, but memory-hungry on high-cardinality columns. For big distinct counts where a ~1% error is fine (daily active users, unique visitors), ClickHouse's approximate uniq() is much cheaper. uniq isn't a dedicated builder method, so reach for the selectExpr escape hatch:

Both counts come back on the same row, which is also a handy way to sanity-check how far off the approximation is for your data before committing to uniq on a dashboard.

Sums, averages, and extremes

.sum(), .avg(), .min(), and .max() all take (column, alias) and map directly to sum, avg, min, and max:

min and max tell you the extreme value. They don't tell you which row produced it — for that, ClickHouse has argMax/argMin, and the builder supports both natively:

.argMax(column, argColumn, alias) returns the value of column on the row where argColumn is largest; .argMin() is the mirror image. This pattern shows up constantly in ClickHouse — "latest row per group" without a self-join — and it's also the standard workaround for deduplicating ReplacingMergeTree data without paying the FINAL cost.

Percentiles: quantile

Averages hide tail latency; percentiles don't. .quantile(column, level, alias) takes a level between 0 and 1 and generates ClickHouse's approximate quantile:

Note that quantile is approximate (reservoir sampling). That's the right default for latency dashboards — it's fast and the error is small. If you need exact quantiles, that's quantileExact in raw SQL via selectExpr.

Spread: stddev and variance

For anomaly detection or "is this metric noisier than usual" questions, the builder exposes .stddev(column, alias) (sample standard deviation, stddevSamp) and .variance(column, alias):

Grouping by time: groupByTimeInterval

Time-series charts are GROUP BY over a bucketed timestamp. The builder's .groupByTimeInterval(column, interval, method?) handles the GROUP BY side: it adds toStartOfInterval(created_at, INTERVAL 1 DAY) (or another toStartOf* function) to the GROUP BY clause. You still select the bucket expression you want returned — selectExpr again:

The method defaults to toStartOfInterval, so '1 day', '15 minute', and '1 week' all work with the same call. The optional third argument switches to a fixed-granularity function — 'toStartOfHour', 'toStartOfDay', 'toStartOfMonth', and the rest of the toStartOf* family — when you want calendar-aligned buckets (the interval string is ignored for those, since the function name carries the granularity):

Bucketing behavior, alias rules, and the DateTime-comes-back-as-a-string detail are covered in depth in ClickHouse toStartOfInterval with GROUP BY in TypeScript.

WITH TOTALS: the grand-total row for free

WITH TOTALS is a distinctly ClickHouse feature: alongside the grouped rows, ClickHouse computes one extra row containing the aggregation over all rows — the grand total — in the same query. No second round trip, no summing on the client.

In the builder it's a single call:

This is exactly what dashboard summary rows want — "revenue by region, plus company-wide total" — and ClickHouse computes the totals row in the same pass over the data. If you've ever fired two queries (one grouped, one ungrouped) to render a table with a footer, .withTotals() replaces the second one.

Filtering groups: having

WHERE filters rows before aggregation; HAVING filters groups after. .having(condition, params?) takes a SQL condition string, with ? placeholders bound safely from the params array:

The condition is raw SQL by design — HAVING clauses reference aggregate expressions, which don't exist as typed columns. Keep row-level filters in .where() (they're fully typed, and ClickHouse can use them to skip data) and reserve .having() for conditions on aggregate results.

Collecting values per group: groupArray

One more GROUP BY companion worth knowing: groupArray() collects a column's values into an array per group — the top events per user, the sequence of statuses per order. It isn't a dedicated builder method, so use selectExpr for it:

What aggregation results look like at runtime

ClickHouse returns some types in ways that surprise TypeScript developers, and hypequery's generated types encode the reality rather than the wish:

• count(), countDistinct(), and integer sum() results are UInt64/Int64 under the hood and arrive as strings in JavaScript (they can exceed Number.MAX_SAFE_INTEGER).
• DateTime buckets from groupByTimeInterval arrive as strings like "2026-07-18 00:00:00".
• Aggregates over Nullable(T) columns can be null when a group has no non-NULL values.

Because the builder's result types reflect this, Number(row.order_count) versus row.order_count is a decision you make consciously at the edge, not a runtime surprise in production.

Where to go from here

• ClickHouse filter operators in TypeScript — the .where() side of every query on this page
• ClickHouse joins in TypeScript — grouping across joined tables
• ClickHouse for time series — where GROUP BY time bucketing becomes the whole workload
• Quick start — generate types from your schema and run your first grouped query