Aggregation Functions

Aggregation functions are used within summarize operators to compute values across groups of rows. Most common aggregations — count(), sum(), avg(), min(), max() — work across all dialects. More advanced aggregations like arg_max(), arg_min(), tdigest(), and hll_merge() are ClickHouse-only or have limited availability.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Function	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
`count()`	✅	✅	✅	✅	✅
`countif()`	✅	✅	✅	✅	✅	Non-ClickHouse: `SUM(CASE WHEN ...)`
`sum()`	✅	✅	✅	✅	✅
`sumif()`	✅	✅	✅	✅	✅	Non-ClickHouse: `SUM(CASE WHEN ...)`
`avg()`	✅	✅	✅	✅	✅
`avgif()`	✅	✅	✅	✅	✅	Non-ClickHouse: `AVG(CASE WHEN ...)`
`min()`	✅	✅	✅	✅	✅
`minif()` / `min_if()`	✅	✅	✅	✅	✅
`max()`	✅	✅	✅	✅	✅
`maxif()` / `max_if()`	✅	✅	✅	✅	✅
`dcount()`	✅	✅	✅	✅	✅	Approximate distinct count. ClickHouse: `uniq()`; DuckDB: `approx_count_distinct()`; others: `COUNT(DISTINCT ...)`
`dcountif()`	✅	✅	✅	✅	✅	ClickHouse: `uniqIf()`; others: `COUNT(DISTINCT CASE WHEN ...)`
`count_distinct()`	✅	✅	✅	✅	✅	Exact distinct count. ClickHouse: `uniqExact()`; others: `COUNT(DISTINCT ...)`
`any()` / `take_any()`	⚠️	⚠️	✅	⚠️	⚠️	KQL returns an arbitrary value. Non-ClickHouse: `MIN()` used as a deterministic placeholder — results may differ since `MIN()` imposes ordering. Use `ANY_VALUE()` on MySQL 8.0+ if available
`anyif()`	✅	✅	✅	✅	✅	Non-ClickHouse: `MIN(CASE WHEN ...)`
`arg_max()`	❌	❌	✅	❌	❌	ClickHouse: `argMax()`. Non-ClickHouse: approximated as `MAX(value)` (different semantics — emits a warning)
`arg_min()`	❌	❌	✅	❌	❌	ClickHouse: `argMin()`. Non-ClickHouse: approximated as `MIN(value)` (different semantics — emits a warning)
`stdev()`	✅	✅	✅	✅	✅	Sample standard deviation. ClickHouse: `stddevSamp()`; SQLite: emulated; others: `STDDEV_SAMP()`
`stdevif()`	⚠️	✅	✅	✅	✅	ClickHouse: `stddevSampIf()`; MySQL/Postgres/DuckDB: `STDDEV_SAMP(CASE WHEN ...)`; SQLite: synthesized from `AVG` — numerically unstable, requires the SQLite math extension, no warning
`stdevp()`	✅	✅	✅	✅	✅	Population standard deviation. ClickHouse: `stddevPop()`; SQLite: emulated; others: `STDDEV_POP()`
`variance()`	✅	✅	✅	✅	✅	Sample variance. ClickHouse: `varSamp()`; SQLite: emulated; others: `VAR_SAMP()`
`varianceif()`	⚠️	✅	✅	✅	✅	ClickHouse: `varSampIf()`; MySQL/Postgres/DuckDB: `VAR_SAMP(CASE WHEN ...)`; SQLite: synthesized from `AVG` — numerically unstable, requires the SQLite math extension, no warning
`variancep()`	✅	✅	✅	✅	✅	Population variance. ClickHouse: `varPop()`; SQLite: emulated; others: `VAR_POP()`
`covariance()` / `covariancep()`	✅	✅	✅	✅	✅	ClickHouse: `covarSamp()` / `covarPop()`; MySQL & SQLite: emulated via `AVG`/`COUNT`; Postgres & DuckDB: `COVAR_SAMP()` / `COVAR_POP()`
`percentile()`	❌	❌	✅	✅	✅	ClickHouse: `quantile()`; Postgres & DuckDB: `percentile_cont() WITHIN GROUP`
`percentiles()`	❌	❌	✅	⚠️	⚠️	ClickHouse: `quantiles(...)`; Postgres & DuckDB: multiple values collapse to a single `percentile_cont` with a warning
`hll()`	✅	✅	✅	✅	✅	ClickHouse: `uniqHLL12()`; DuckDB: `approx_count_distinct()`; others: `COUNT(DISTINCT ...)`
`hll_merge()`	❌	❌	✅	❌	❌	ClickHouse: `uniqHLL12Merge()`. Non-ClickHouse: approximated as `COUNT(DISTINCT)` (emits a warning)
`tdigest()` / `tdigest_merge()`	❌	❌	✅	❌	❌	ClickHouse: `quantileTDigest()`. Postgres & DuckDB: approximated as `percentile_cont(0.5)` (emits a warning)
`make_list()`	❌	✅	✅	✅	✅	ClickHouse: `groupArray`; Postgres & DuckDB: `array_agg`; MySQL: `JSON_ARRAYAGG`. An optional size-limit argument is dropped with a warning — the array is unbounded
`make_set()`	❌	⚠️	✅	⚠️	⚠️	ClickHouse: `groupUniqArray`, which deduplicates. Postgres, DuckDB and MySQL lower it to the same aggregate as `make_list` — `array_agg` / `JSON_ARRAYAGG`, with no `DISTINCT` — so duplicates survive and the set semantics are silently lost (no warning)
`make_bag()`	❌	⚠️	⚠️	⚠️	⚠️	Emits a JSON array, not a merged dictionary — keys are not combined (semantic approximation, no warning): ClickHouse `groupArray()`, MySQL `JSON_ARRAYAGG`, Postgres `json_agg`, DuckDB `json_group_array`; SQLite: unsupported
`coalesce()`	✅	✅	✅	✅	✅	`COALESCE()`
`nullif()`	✅	✅	✅	✅	✅	`NULLIF()`
`case()`	✅	✅	✅	✅	✅	`CASE WHEN ... END`
`iif()` / `iff()`	✅	✅	✅	✅	✅	ClickHouse: `if()`; others: `CASE WHEN`