Aggregate Functions - ClickHouse Documentation

ClickHouse supports all standard SQL aggregate functions (sum, avg, min, max, count), as well as a wide range of other aggregate functions.

Page	Description
groupFormat	Formats rows in each group using an output format and returns the formatted data as a string.
aggThrow	This function can be used for the purpose of testing exception safety. It will throw an exception on creation with the specified probability.
analysisOfVariance	Provides a statistical test for one-way analysis of variance (ANOVA test). It is a test over several groups of normally distributed observations to find out whether all groups have the same mean or not.
any	Selects the first encountered value of a column.
anyHeavy	Selects a frequently occurring value using the heavy hitters algorithm. If there is a value that occurs more than in half the cases in each of the query execution threads, this value is returned. Normally, the result is nondeterministic.
anyLast	Selects the last encountered value of a column.
approx_top_k	Returns an array of the approximately most frequent values and their counts in the specified column.
approx_top_sum	Returns an array of the approximately most frequent values and their counts in the specified column.
argAndMax	Calculates the `arg` and `val` value for a maximum `val` value. If there are multiple rows with equal `val` being the maximum, which of the associated `arg` and `val` is returned is not deterministic.
argAndMin	Calculates the `arg` and `val` value for a minimum `val` value. If there are multiple rows with equal `val` being the minimum, which of the associated `arg` and `val` is returned is not deterministic.
argMax	Calculates the `arg` value for a maximum `val` value.
argMin	Calculates the `arg` value for a minimum `val` value. If there are multiple rows with equal `val` being the maximum, which of the associated `arg` is returned is not deterministic.
avg	Calculates the arithmetic mean.
avgWeighted	Calculates the weighted arithmetic mean.
boundingRatio	Aggregate function that calculates the slope between the leftmost and rightmost points across a group of values.
categoricalInformationValue	Calculates the value of `(P(tag = 1) - P(tag = 0))(log(P(tag = 1)) - log(P(tag = 0)))` for each category.
contingency	The `contingency` function calculates the contingency coefficient, a value that measures the association between two columns in a table. The computation is similar to the `cramersV` function but with a different denominator in the square root.
corr	Calculates the Pearson correlation coefficient.
corrMatrix	Computes the correlation matrix over N variables.
corrStable	Calculates the Pearson correlation coefficient, but uses a numerically stable algorithm.
count	Counts the number of rows or not-NULL values.
covarPop	Calculates the population covariance
covarPopMatrix	Returns the population covariance matrix over N variables.
covarPopStable	Calculates the value of the population covariance
covarSamp	Calculates the value of `Σ((x - x̅)(y - y̅)) / (n - 1)`
covarSampMatrix	Returns the sample covariance matrix over N variables.
covarSampStable	Similar to covarSamp but works slower while providing a lower computational error.
cramersV	The result of the `cramersV` function ranges from 0 (corresponding to no association between the variables) to 1 and can reach 1 only when each value is completely determined by the other. It may be viewed as the association between two variables as a percentage of their maximum possible variation.
cramersVBiasCorrected	Calculates Cramer’s V, but uses a bias correction.
deltaSum	Sums the arithmetic difference between consecutive rows.
deltaSumTimestamp	Adds the difference between consecutive rows. If the difference is negative, it is ignored.
distinctDynamicTypes	Calculates the list of distinct data types stored in Dynamic column.
distinctJSONPaths	Calculates a list of distinct paths stored in a JSON column.
distinctJSONPathsAndTypes	Calculates the list of distinct paths and their types stored in JSON
entropy	Calculates Shannon entropy of for a column of values.
estimateCompressionRatio	Estimates the compression ratio of a given column without compressing it.
exponentialMovingAverage	Calculates the exponential moving average of values for the determined time.
exponentialTimeDecayedAvg	Returns the exponentially smoothed weighted moving average of values of a time series at point `t` in time.
exponentialTimeDecayedCount	Returns the cumulative exponential decay over a time series at the index `t` in time.
exponentialTimeDecayedMax	Returns the maximum of the computed exponentially smoothed moving average at index `t` in time with that at `t-1`.
exponentialTimeDecayedSum	Returns the sum of exponentially smoothed moving average values of a time series at the index `t` in time.
first_value	It is an alias for any but it was introduced for compatibility with Window Functions, where sometimes it is necessary to process `NULL` values (by default all ClickHouse aggregate functions ignore NULL values).
flameGraph	Aggregate function which builds a flamegraph using the list of stacktraces.
groupArray	Creates an array of argument values. Values can be added to the array in any (indeterminate) order.
groupArrayArray	Aggregates arrays into a larger array of those arrays.
groupArrayInsertAt	Inserts a value into the array at the specified position.
groupArrayIntersect	Return an intersection of given arrays (Return all items of arrays, that are in all given arrays).
groupArrayLast	Creates an array of the last argument values.
groupArrayMovingAvg	Calculates the moving average of input values.
groupArrayMovingSum	Calculates the moving sum of input values.
groupArraySample	Creates an array of sample argument values. The size of the resulting array is limited to `max_size` elements. Argument values are selected and added to the array randomly.
groupArraySorted	Returns an array with the first N items in ascending order.
groupBitAnd	Applies bit-wise `AND` for series of numbers.
groupBitmap	Bitmap or Aggregate calculations from a unsigned integer column, return cardinality of type UInt64, if add suffix -State, then return a bitmap object
groupBitmapAnd	Calculations the AND of a bitmap column, return cardinality of type UInt64, if add suffix -State, then return a bitmap object.
groupBitmapOr	Calculations the OR of a bitmap column, return cardinality of type UInt64, if add suffix -State, then return a bitmap object. This is equivalent to `groupBitmapMerge`.
groupBitmapXor	Calculates the XOR of a bitmap column, and returns the cardinality of type UInt64, if used with suffix -State, then it returns a bitmap object
groupBitOr	Applies bit-wise `OR` to a series of numbers.
groupBitXor	Applies bit-wise `XOR` for series of numbers.
groupConcat	Calculates a concatenated string from a group of strings, optionally separated by a delimiter, and optionally limited by a maximum number of elements.
groupUniqArray	Creates an array from different argument values.
intervalLengthSum	Calculates the total length of union of all ranges (segments on numeric axis).
kolmogorovSmirnovTest	Applies Kolmogorov-Smirnov’s test to samples from two populations.
kurtPop	Computes the kurtosis of a sequence.
kurtSamp	Computes the sample kurtosis of a sequence.
largestTriangleThreeBuckets	Applies the Largest-Triangle-Three-Buckets algorithm to the input data.
last_value	Selects the last encountered value, similar to `anyLast`, but could accept NULL.
mannWhitneyUTest	Applies the Mann-Whitney rank test to samples from two populations.
max	Aggregate function that calculates the maximum across a group of values.
maxIntersections	Aggregate function that calculates the maximum number of times that a group of intervals intersects each other (if all the intervals intersect at least once).
maxIntersectionsPosition	Aggregate function that calculates the positions of the occurrences of the maxIntersections function.
maxMap	Calculates the maximum from `value` array according to the keys specified in the `key` array.
meanZTest	Applies mean z-test to samples from two populations.
median	The `median` functions are the aliases for the corresponding `quantile` functions. They calculate median of a numeric data sample.
min	Aggregate function that calculates the minimum across a group of values.
minMap	Calculates the minimum from `value` array according to the keys specified in the `key` array.
quantile	Computes an approximate quantile of a numeric data sequence.
quantileBFloat16	Computes an approximate quantile of a sample consisting of bfloat16 numbers.
quantileDD	Computes an approximate quantile of a sample with relative-error guarantees.
quantileDeterministic	Computes an approximate quantile of a numeric data sequence.
quantileExact Functions	quantileExact, quantileExactLow, quantileExactHigh, quantileExactExclusive, quantileExactInclusive functions
quantileExactExclusive	Exactly computes the quantile of a numeric data sequence.
quantileExactHigh	Similar to quantileExact, this computes the exact quantile of a numeric data sequence.
quantileExactInclusive	Exactly computes the quantile of a numeric data sequence.
quantileExactLow	Similar to quantileExact, this computes the exact quantile of a numeric data sequence.
quantileExactWeighted	Exactly computes the quantile of a numeric data sequence, taking into account the weight of each element.
quantileExactWeightedInterpolated	Computes quantile of a numeric data sequence using linear interpolation, taking into account the weight of each element.
quantileGK	Computes the quantile of a numeric data sequence using the Greenwald-Khanna algorithm.
quantileInterpolatedWeighted	Computes quantile of a numeric data sequence using linear interpolation, taking into account the weight of each element.
quantilePrometheusHistogram	Computes quantile of a histogram using linear interpolation.
quantiles Functions	quantiles, quantilesExactExclusive, quantilesExactInclusive, quantilesGK
quantilesExactExclusive	Exactly computes the quantiles of a numeric data sequence.
quantilesExactInclusive	Exactly computes the quantiles of a numeric data sequence.
quantilesGK	quantilesGK works similarly to quantileGK but allows us to calculate quantities at different levels simultaneously and returns an array.
quantilesTimingWeighted	With the determined precision computes the quantile of a numeric data sequence according to the weight of each sequence member.
quantileTDigest	Computes an approximate quantile of a numeric data sequence using the t-digest algorithm.
quantileTDigestWeighted	Computes an approximate quantile of a numeric data sequence using the t-digest algorithm.
quantileTiming	With the determined precision computes the quantile of a numeric data sequence.
quantileTimingWeighted	With the determined precision computes the quantile of a numeric data sequence according to the weight of each sequence member.
rankCorr	Computes a rank correlation coefficient.
simpleLinearRegression	Performs simple (unidimensional) linear regression.
singleValueOrNull	The aggregate function `singleValueOrNull` is used to implement subquery operators, such as `x = ALL (SELECT ...)`. It checks if there is only one unique non-NULL value in the data.
skewPop	Computes the skewness of a sequence.
skewSamp	Computes the sample skewness of a sequence.
sparkbar	The function plots a frequency histogram for values `x` and the repetition rate `y` of these values over the interval `[min_x, max_x]`.
stddevPop	The result is equal to the square root of varPop.
stddevPopStable	The result is equal to the square root of varPop. Unlike stddevPop, this function uses a numerically stable algorithm.
stddevSamp	The result is equal to the square root of varSamp
stddevSampStable	The result is equal to the square root of varSamp. Unlike this function uses a numerically stable algorithm.
stochasticLinearRegression	This function implements stochastic linear regression. It supports custom parameters for learning rate, L2 regularization coefficient, mini-batch size, and has a few methods for updating weights (Adam, simple SGD, Momentum, Nesterov.)
stochasticLogisticRegression	This function implements stochastic logistic regression. It can be used for binary classification problem, supports the same custom parameters as stochasticLinearRegression and works the same way.
studentTTest	Applies the student t-test to samples from two populations.
studentTTestOneSample	Applies the one-sample Student t-test to a sample and a known population mean.
sum	Calculates the sum. Only works for numbers.
sumCount	Calculates the sum of the numbers and counts the number of rows at the same time. The function is used by ClickHouse query optimizer: if there are multiple `sum`, `count` or `avg` functions in a query, they can be replaced to single `sumCount` function to reuse the calculations. The function is rarely needed to use explicitly.
sumKahan	Calculates the sum of the numbers with Kahan compensated summation algorithm
sumMap	Totals one or more `value` arrays according to the keys specified in the `key` array. Returns a tuple of arrays: keys in sorted order, followed by values summed for the corresponding keys without overflow.
sumMapWithOverflow	Totals a `value` array according to the keys specified in the `key` array. Returns a tuple of two arrays: keys in sorted order, and values summed for the corresponding keys. Differs from the sumMap function in that it does summation with overflow.
sumWithOverflow	Computes the sum of the numbers, using the same data type for the result as for the input parameters. If the sum exceeds the maximum value for this data type, it is calculated with overflow.
theilsU	The `theilsU` function calculates Theils’ U uncertainty coefficient, a value that measures the association between two columns in a table.
timeSeriesChangesToGrid	Aggregate function that calculates PromQL-like changes over time series data on the specified grid.
timeSeriesDeltaToGrid	Aggregate function that calculates PromQL-like delta over time series data on the specified grid.
timeSeriesDerivToGrid	Aggregate function that calculates PromQL-like derivative over time series data on the specified grid.
timeSeriesGroupArray	Sorts time series by timestamp in ascending order.
timeSeriesInstantDeltaToGrid	Aggregate function that calculates PromQL-like idelta over time series data on the specified grid.
timeSeriesInstantRateToGrid	Aggregate function that calculates PromQL-like irate over time series data on the specified grid.
timeSeriesLastTwoSamples	Aggregate function for re-sampling time series data for PromQL-like irate and idelta calculation
timeSeriesPredictLinearToGrid	Aggregate function that calculates PromQL-like linear prediction over time series data on the specified grid.
timeSeriesRateToGrid	Aggregate function that calculates PromQL-like rate over time series data on the specified grid.
timeSeriesResampleToGridWithStaleness	Aggregate function that re-samples time series data to the specified grid.
timeSeriesResetsToGrid	Aggregate function that calculates PromQL-like resets over time series data on the specified grid.
topK	Returns an array of the approximately most frequent values in the specified column. The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).
topKWeighted	Returns an array of the approximately most frequent values in the specified column. The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves). Additionally, the weight of the value is taken into account.
uniq	Calculates the approximate number of different values of the argument.
uniqCombined	Calculates the approximate number of different argument values.
uniqCombined64	Calculates the approximate number of different argument values. It is the same as uniqCombined, but uses a 64-bit hash for all data types rather than just for the String data type.
uniqExact	Calculates the exact number of different argument values.
uniqHLL12	Calculates the approximate number of different argument values, using the HyperLogLog algorithm.
uniqTheta	Calculates the approximate number of different argument values, using the Theta Sketch Framework.
varPop	Calculates the population variance.
varPopStable	Returns the population variance. Unlike varPop , this function uses a numerically stable algorithm. It works slower but provides a lower computational error.
varSamp	Calculate the sample variance of a data set.
varSampStable	Calculate the sample variance of a data set. Unlike `varSamp` , this function uses a numerically stable algorithm. It works slower but provides a lower computational error.
welchTTest	Applies Welch’s t-test to samples from two populations.