ClickHouse/clickhouse-docs-embeddings huggingface dataset:
stddevSamp

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/stddevSamp.md)

## stddevSamp[​](#stddevSamp "Direct link to stddevSamp")

Introduced in: v1.1.0

Returns the sample standard deviation of a numeric data sequence.
The result is equal to the square root of [`varSamp`](/docs/sql-reference/aggregate-functions/reference/varSamp).

Note

This function uses a numerically unstable algorithm.
If you need [numerical stability](https://en.wikipedia.org/wiki/Numerical_stability) in calculations, use the [`stddevSampStable`](/docs/sql-reference/aggregate-functions/reference/stddevsampstable) function. It works slower but provides a lower computational error.

**Syntax**

```
stddevSamp(x)
```

**Aliases**: `STDDEV_SAMP`

**Arguments**

- `x` — Values for which to find the square root of sample variance. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns the square root of sample variance of `x`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Computing sample standard deviation**

```
DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data
(
    population UInt8,
)
ENGINE = Log;

INSERT INTO test_data VALUES (3),(3),(3),(4),(4),(5),(5),(7),(11),(15);

SELECT
    stddevSamp(population)
FROM test_data;
```

```
┌─stddevSamp(population)─┐
│                      4 │
└────────────────────────┘
```

- [stddevSamp](#stddevSamp)

Was this page helpful?
stddevSampStable

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/stddevSampStable.md)

## stddevSampStable[​](#stddevSampStable "Direct link to stddevSampStable")

Introduced in: v1.1.0

The result is equal to the square root of [varSamp](/docs/sql-reference/aggregate-functions/reference/varSamp). Unlike [stddevSamp](/docs/sql-reference/aggregate-functions/reference/stddevsamp) this function uses a numerically stable algorithm. It works slower but provides a lower computational error.

**Syntax**

```
stddevSampStable(x)
```

**Arguments**

- `x` — Values for which to find the square root of sample variance. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns the square root of sample variance of `x`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic usage**

```
DROP TABLE IF EXISTS test_data;
CREATE TABLE test_data
(
    population UInt8,
)
ENGINE = Log;

INSERT INTO test_data VALUES (3),(3),(3),(4),(4),(5),(5),(7),(11),(15);

SELECT
    stddevSampStable(population)
FROM test_data;
```

```
┌─stddevSampStable(population)─┐
│                            4 │
└──────────────────────────────┘
```

- [stddevSampStable](#stddevSampStable)

Was this page helpful?
studentTTest

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/studentTTest.md)

## studentTTest[​](#studentTTest "Direct link to studentTTest")

Introduced in: v21.1.0

Applies Student's t-test to samples from two populations.

Values of both samples are in the `sample_data` column. If `sample_index` equals to 0 then the value in that row belongs to the sample from the first population. Otherwise it belongs to the sample from the second population.
The null hypothesis is that means of populations are equal. Normal distribution with equal variances is assumed.

**See Also**

- [Student's t-test](https://en.wikipedia.org/wiki/Student%27s_t-test)
- [welchTTest function](/docs/sql-reference/aggregate-functions/reference/welchttest)

**Syntax**

```
studentTTest([confidence_level])(sample_data, sample_index)
```

**Parameters**

- `confidence_level` — Optional. Confidence level in order to calculate confidence intervals. [`Float`](/docs/sql-reference/data-types/float)

**Arguments**

- `sample_data` — Sample data. [`Integer`](/docs/sql-reference/data-types/int-uint) or [`Float`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `sample_index` — Sample index. [`Integer`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple with two or four elements (if the optional `confidence_level` is specified): calculated t-statistic, calculated p-value, [calculated confidence-interval-low], [calculated confidence-interval-high]. [`Tuple(Float64, Float64)`](/docs/sql-reference/data-types/tuple) or [`Tuple(Float64, Float64, Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
SELECT studentTTest(sample_data, sample_index) FROM student_ttest;
```

```
┌─studentTTest(sample_data, sample_index)───┐
│ (-0.21739130434783777,0.8385421208415731) │
└───────────────────────────────────────────┘
```

**See Also**

- [Student's t-test](https://en.wikipedia.org/wiki/Student%27s_t-test)
- [welchTTest function](/docs/sql-reference/aggregate-functions/reference/welchttest)

- [studentTTest](#studentTTest)

Was this page helpful?
studentTTestOneSample

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/studentTTestOneSample.md)

## studentTTestOneSample[​](#studentTTestOneSample "Direct link to studentTTestOneSample")

Introduced in: v25.10.0

Applies the one-sample Student's t-test to determine whether the mean of a sample differs from a known population mean.

Normality is assumed. The null hypothesis is that the sample mean equals the population mean.

The optional `confidence_level` enables confidence interval calculation.

**Notes:**

- At least 2 observations are required; otherwise the result is `(nan, nan)` (and intervals if requested are `nan`).
- Constant or near-constant input will also return `nan` due to zero (or effectively zero) standard error.

**See Also**

- [Student's t-test](https://en.wikipedia.org/wiki/Student%27s_t-test)
- [studentTTest function](/docs/sql-reference/aggregate-functions/reference/studentttest)

**Syntax**

```
studentTTestOneSample([confidence_level])(sample_data, population_mean)
```

**Parameters**

- `confidence_level` — Optional. Confidence level for confidence intervals. Float in (0, 1). [`Float*`](/docs/sql-reference/data-types/float)

**Arguments**

- `sample_data` — Sample data. [`Integer`](/docs/sql-reference/data-types/int-uint) or [`Float`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `population_mean` — Known population mean to test against (usually a constant). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a tuple with two or four elements (if `confidence_level` is specified): calculated t-statistic, calculated p-value (two-tailed), [calculated confidence-interval-low], [calculated confidence-interval-high]. Confidence intervals are for the sample mean at the given confidence level. [`Tuple(Float64, Float64)`](/docs/sql-reference/data-types/tuple) or [`Tuple(Float64, Float64, Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Without confidence interval**

```
SELECT studentTTestOneSample()(value, 20.0) FROM t;
```

**With confidence interval (95%)**

```
SELECT studentTTestOneSample(0.95)(value, 20.0) FROM t;
```

**See Also**

- [Student's t-test](https://en.wikipedia.org/wiki/Student%27s_t-test)
- [studentTTest function](/docs/sql-reference/aggregate-functions/reference/studentttest)

- [studentTTestOneSample](#studentTTestOneSample)

Was this page helpful?
sum

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/sum.md)

## sum[​](#sum "Direct link to sum")

Introduced in: v1.1.0

Calculates the sum of numeric values.

**Syntax**

```
sum(num)
```

**Arguments**

- `num` — Column of numeric values. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns the sum of the values. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Examples**

**Computing sum of employee salaries**

```
CREATE TABLE employees
(
    id UInt32,
    name String,
    salary UInt32
)
ENGINE = Memory;

INSERT INTO employees VALUES
    (87432, 'John Smith', 45680),
    (59018, 'Jane Smith', 72350),
    (20376, 'Ivan Ivanovich', 58900),
    (71245, 'Anastasia Ivanovna', 89210);

SELECT sum(salary) FROM employees;
```

```
┌─sum(salary)─┐
│      266140 │
└─────────────┘
```

- [sum](#sum)

Was this page helpful?
sumCount

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/sumCount.md)

## sumCount[​](#sumCount "Direct link to sumCount")

Introduced in: v21.6.0

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.

**See also**

- [`optimize_syntax_fuse_functions`](/docs/operations/settings/settings#optimize_syntax_fuse_functions) setting.

**Syntax**

```
sumCount(x)
```

**Arguments**

- `x` — Input value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a tuple `(sum, count)`, where `sum` is the sum of numbers and `count` is the number of rows with not-NULL values. [`Tuple`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
CREATE TABLE s_table (x Int8) ENGINE = Log;
INSERT INTO s_table SELECT number FROM numbers(0, 20);
INSERT INTO s_table VALUES (NULL);
SELECT sumCount(x) FROM s_table;
```

```
┌─sumCount(x)─┐
│ (190,20)    │
└─────────────┘
```

**See also**

- [optimize\_syntax\_fuse\_functions](/docs/operations/settings/settings#optimize_syntax_fuse_functions) setting.

- [sumCount](#sumCount)

Was this page helpful?
sumKahan

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/sumKahan.md)

## sumKahan[​](#sumKahan "Direct link to sumKahan")

Introduced in: v1.1.0

Calculates the sum of the numbers with [Kahan compensated summation algorithm](https://en.wikipedia.org/wiki/Kahan_summation_algorithm).
Slower than [`sum`](/docs/sql-reference/aggregate-functions/reference/sum) function.
The compensation works only for [Float](/docs/sql-reference/data-types/float) types.

**Syntax**

```
sumKahan(x)
```

**Arguments**

- `x` — Input value. [`Integer`](/docs/sql-reference/data-types/int-uint) or [`Float`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns the sum of numbers. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Examples**

**Demonstrating precision improvement with Kahan summation**

```
SELECT sum(0.1), sumKahan(0.1) FROM numbers(10);
```

```
┌───────────sum(0.1)─┬─sumKahan(0.1)─┐
│ 0.9999999999999999 │             1 │
└────────────────────┴───────────────┘
```

- [sumKahan](#sumKahan)

Was this page helpful?
sumWithOverflow

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/sumWithOverflow.md)

## sumWithOverflow[​](#sumWithOverflow "Direct link to sumWithOverflow")

Introduced in: v1.1.0

Computes a sum of numeric values, 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.

**Syntax**

```
sumWithOverflow(num)
```

**Arguments**

- `num` — Column of numeric values. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Returned value**

The sum of the values. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal)

**Examples**

**Demonstrating overflow behavior with UInt16**

```
CREATE TABLE employees
(
    id UInt32,
    name String,
    monthly_salary UInt16 -- selected so that the sum of values produces an overflow
)
ENGINE = Memory;

INSERT INTO employees VALUES
    (1, 'John', 20000),
    (2, 'Jane', 18000),
    (3, 'Bob', 12000),
    (4, 'Alice', 10000),
    (5, 'Charlie', 8000);

-- Query for the total amount of the employee salaries using the sum and sumWithOverflow functions and show their types using the toTypeName function
-- For the sum function the resulting type is UInt64, big enough to contain the sum, whilst for sumWithOverflow the resulting type remains as UInt16.

SELECT
    sum(monthly_salary) AS no_overflow,
    sumWithOverflow(monthly_salary) AS overflow,
    toTypeName(no_overflow),
    toTypeName(overflow)
FROM employees;
```

```
┌─no_overflow─┬─overflow─┬─toTypeName(no_overflow)─┬─toTypeName(overflow)─┐
│       68000 │     2464 │ UInt64                  │ UInt16               │
└─────────────┴──────────┴─────────────────────────┴──────────────────────┘
```

- [sumWithOverflow](#sumWithOverflow)

Was this page helpful?
theilsU

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/theilsU.md)

## theilsU[​](#theilsU "Direct link to theilsU")

Introduced in: v22.1.0

The `theilsU` function calculates the [Theil's U uncertainty coefficient](https://en.wikipedia.org/wiki/Contingency_table#Uncertainty_coefficient), a value that measures the association between two columns in a table.
Its values range from −1.0 (100% negative association, or perfect inversion) to +1.0 (100% positive association, or perfect agreement).
A value of 0.0 indicates the absence of association.

**Syntax**

```
theilsU(column1, column2)
```

**Arguments**

- `column1` — First column to be compared. [`Any`](/docs/sql-reference/data-types)
- `column2` — Second column to be compared. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a value between -1 and 1. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT theilsU(a, b)
FROM (
    SELECT
        number % 10 AS a,
        number % 4 AS b
    FROM
        numbers(150)
);
```

```
┌────────theilsU(a, b)─┐
│ -0.30195720557678846 │
└──────────────────────┘
```

- [theilsU](#theilsU)

Was this page helpful?
timeSeriesGroupArray

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/timeSeriesGroupArray.md)

## timeSeriesGroupArray[​](#timeSeriesGroupArray "Direct link to timeSeriesGroupArray")

Introduced in: v25.9.0

Sorts time series data by timestamp in ascending order.

Note

This function is experimental, enable it by setting `allow_experimental_ts_to_grid_aggregate_function=true`.

**Syntax**

```
timeSeriesGroupArray(timestamp, value)
```

**Arguments**

- `timestamp` — Timestamp of the sample. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`UInt32`](/docs/sql-reference/data-types/int-uint) or [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `value` — Value of the time series corresponding to the timestamp. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns an array of tuples `(timestamp, value)` sorted by timestamp in ascending order. If there are multiple values for the same timestamp then the function chooses the greatest of these values. [`Array(Tuple(T1, T2))`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage with individual values**

```
WITH
    [110, 120, 130, 140, 140, 100]::Array(UInt32) AS timestamps,
    [1, 6, 8, 17, 19, 5]::Array(Float32) AS values
SELECT timeSeriesGroupArray(timestamp, value)
FROM
(
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);
```

```
┌─timeSeriesGroupArray(timestamp, value)───────────────┐
│ [(100, 5), (110, 1), (120, 6), (130, 8), (140, 19)]  │
└──────────────────────────────────────────────────────┘
```

**Passing multiple samples of timestamps and values as arrays of equal size**

```
WITH
    [110, 120, 130, 140, 140, 100]::Array(UInt32) AS timestamps,
    [1, 6, 8, 17, 19, 5]::Array(Float32) AS values
SELECT timeSeriesGroupArray(timestamps, values);
```

```
┌─timeSeriesGroupArray(timestamps, values)──────────────┐
│ [(100, 5), (110, 1), (120, 6), (130, 8), (140, 19)]   │
└───────────────────────────────────────────────────────┘
```

- [timeSeriesGroupArray](#timeSeriesGroupArray)

Was this page helpful?
topK

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/topK.md)

## topK[​](#topK "Direct link to topK")

Introduced in: v1.1.0

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).

Implements the [Filtered Space-Saving](https://doi.org/10.1016/j.ins.2010.08.024) algorithm for analyzing TopK, based on the reduce-and-combine algorithm from [Parallel Space Saving](https://doi.org/10.1016/j.ins.2015.09.003).

This function does not provide a guaranteed result. In certain situations, errors might occur and it might return frequent values that aren't the most frequent values.

**See Also**

- [topKWeighted](/docs/sql-reference/aggregate-functions/reference/topkweighted)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)
- [approx\_top\_sum](/docs/sql-reference/aggregate-functions/reference/approxtopsum)

**Syntax**

```
topK(N)(column)
topK(N, load_factor)(column)
topK(N, load_factor, 'counts')(column)
```

**Parameters**

- `N` — The number of elements to return. Default value: 10. Maximum value of `N = 65536`. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `load_factor` — Optional. Defines, how many cells reserved for values. If `uniq(column) > N * load_factor`, result of topK function will be approximate. Default value: 3. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `counts` — Optional. Defines whether the result should contain an approximate count and error value. [`Bool`](/docs/sql-reference/data-types/boolean)

**Arguments**

- `column` — The name of the column for which to find the most frequent values. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns an array of the approximately most frequent values, sorted in descending order of approximate frequency. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT topK(3)(AirlineID) AS res
FROM ontime;
```

```
┌─res─────────────────┐
│ [19393,19790,19805] │
└─────────────────────┘
```

**See Also**

- [topKWeighted](/docs/sql-reference/aggregate-functions/reference/topkweighted)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)
- [approx\_top\_sum](/docs/sql-reference/aggregate-functions/reference/approxtopsum)

- [topK](#topK)

Was this page helpful?
topKWeighted

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/topKWeighted.md)

## topKWeighted[​](#topKWeighted "Direct link to topKWeighted")

Introduced in: v1.1.0

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.

**See Also**

- [topK](/docs/sql-reference/aggregate-functions/reference/topk)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)
- [approx\_top\_sum](/docs/sql-reference/aggregate-functions/reference/approxtopsum)

**Syntax**

```
topKWeighted(N)(column, weight)
topKWeighted(N, load_factor)(column, weight)
topKWeighted(N, load_factor, 'counts')(column, weight)
```

**Parameters**

- `N` — The number of elements to return. Default value: 10. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `load_factor` — Optional. Defines, how many cells reserved for values. If `uniq(column) > N * load_factor`, result of topK function will be approximate. Default value: 3. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `counts` — Optional. Defines whether the result should contain an approximate count and error value. [`Bool`](/docs/sql-reference/data-types/boolean)

**Arguments**

- `column` — The name of the column for which to find the most frequent values. - `weight` — The weight. Every value is accounted `weight` times for frequency calculation. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of the values with maximum approximate sum of weights. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT topKWeighted(2)(k, w) FROM
VALUES('k Char, w UInt64', ('y', 1), ('y', 1), ('x', 5), ('y', 1), ('z', 10));
```

```
┌─topKWeighted(2)(k, w)──┐
│ ['z','x']              │
└────────────────────────┘
```

**With counts parameter**

```
SELECT topKWeighted(2, 10, 'counts')(k, w)
FROM VALUES('k Char, w UInt64', ('y', 1), ('y', 1), ('x', 5), ('y', 1), ('z', 10));
```

```
┌─topKWeighted(2, 10, 'counts')(k, w)─┐
│ [('z',10,0),('x',5,0)]              │
└─────────────────────────────────────┘
```

**See Also**

- [topK](/docs/sql-reference/aggregate-functions/reference/topk)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)
- [approx\_top\_sum](/docs/sql-reference/aggregate-functions/reference/approxtopsum)

- [topKWeighted](#topKWeighted)

Was this page helpful?