Aggregation

The query builder provides typed methods for ClickHouse’s common and analytical aggregates. Chain them after table(), select the grouping dimensions you want back, and call groupBy() before execution.

Aggregate methods

The alias is optional. Providing one keeps result names readable and makes them available to orderBy().

Revenue by region

const revenueByRegion = await db
  .table('orders')
  .select(['region'])
  .sum('amount', 'revenue')
  .count('id', 'order_count')
  .avg('amount', 'average_order_value')
  .groupBy('region')
  .orderBy('revenue', 'DESC')
  .execute();

Multiple dimensions can be selected and grouped together:

const revenueByRegionAndStatus = await db
  .table('orders')
  .select(['region', 'status'])
  .sum('amount', 'revenue')
  .groupBy(['region', 'status'])
  .execute();

Repeated groupBy() calls are additive, so this is equivalent:

.groupBy('region')
.groupBy('status')

Distinct counts

const activeUsers = await db
  .table('events')
  .select(['event_type'])
  .countDistinct('user_id', 'unique_users')
  .groupBy('event_type')
  .execute();

The public method is countDistinct(). It replaces the older distinctCount() spelling found in stale examples.

Percentiles and distribution statistics

const latency = await db
  .table('requests')
  .select(['service'])
  .quantile('latency_ms', 0.5, 'median_latency')
  .quantile('latency_ms', 0.95, 'p95_latency')
  .quantile('latency_ms', 0.99, 'p99_latency')
  .stddev('latency_ms', 'latency_stddev')
  .variance('latency_ms', 'latency_variance')
  .groupBy('service')
  .execute();

quantile() uses ClickHouse’s approximate quantile(level) aggregate. level must be between 0 and 1. The semantic dataset layer calls the same concept measure.percentile() and provides measure.median() as percentile 0.5.

argMax and argMin

Use the arg aggregates when you need one field from the row with the latest, earliest, greatest, or smallest value of another field:

const accountState = await db
  .table('account_events')
  .select(['account_id'])
  .argMax('status', 'created_at', 'latest_status')
  .argMin('status', 'created_at', 'first_status')
  .groupBy('account_id')
  .execute();

This is a direct typed expression of argMax(status, created_at) and argMin(status, created_at) in ClickHouse.

Time intervals

const dailyRevenue = await db
  .table('orders')
  .groupByTimeInterval('created_at', '1 day')
  .sum('amount', 'revenue')
  .orderBy('created_at', 'ASC')
  .execute();

groupByTimeInterval() generates a ClickHouse time bucket and adds the corresponding grouping expression. Use the exported time-expression helpers when you need a specific alias or want to compose the expression yourself.

Filter before grouping

const completedRevenue = await db
  .table('orders')
  .where('status', 'eq', 'completed')
  .where('created_at', 'gte', '2026-01-01')
  .select(['region'])
  .sum('amount', 'revenue')
  .groupBy('region')
  .execute();

where() filters source rows before aggregation. having() filters the grouped result:

const largeRegions = await db
  .table('orders')
  .select(['region'])
  .sum('amount', 'revenue')
  .groupBy('region')
  .having('revenue > ?', [10000])
  .execute();

Totals

withTotals() maps to ClickHouse GROUP BY ... WITH TOTALS:

const result = await db
  .table('orders')
  .select(['region'])
  .sum('amount', 'revenue')
  .groupBy('region')
  .withTotals()
  .execute();

Expressions and window aggregates

For a window function, add a trusted SQL expression to the typed selection:

import { selectExpr } from '@hypequery/clickhouse';

const ranked = await db
  .table('orders')
  .select([
    'region',
    'amount',
    selectExpr(
      'row_number() OVER (PARTITION BY region ORDER BY amount DESC)',
      'rank',
    ),
  ])
  .execute();

See What hypequery supports today for the complete builder and semantic aggregate matrix.