> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Aggregate Functions

export const OcientDataIntelligencePlatform = "OcientAIQ™ Unified Data Platform";

The {OcientDataIntelligencePlatform} has aggregated and sorted aggregate functions. Aggregate and sorted aggregate functions compute a single result from a group of rows. You can use the `DISTINCT` keyword with any aggregation function, such as `COUNT(DISTINCT col)`. Whereas sorted aggregate functions use the standard ORDER BY syntax to dictate the ordering of the aggregation. To sort the elements of an array, use the [ARRAY\_SORT](/transform-data-in-data-pipelines#array-data-transformation-functions) function during data load.

## Aggregate Functions

Supported input types vary by function. In general, numeric types include `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `FLOAT`, `DOUBLE`, and `DECIMAL`.

Various examples on this page use these tables.

```sql SQL theme={null}
CREATE TABLE sample_data (val INT, label VARCHAR(10));
INSERT INTO sample_data (val, label) VALUES
    (2, 'alpha'), (4, 'beta'), (4, 'alpha'), (5, NULL),
    (7, 'gamma'), (8, 'alpha'), (12, 'beta'), (15, 'gamma');
```

```sql SQL theme={null}
CREATE TABLE predictions (actual INT, predicted INT);
INSERT INTO predictions (actual, predicted) VALUES
    (10, 12), (20, 18), (30, 33),
    (40, 37), (50, 52), (60, 58);
```

For simple functions, examples use the `sys.dummy` virtual table. For details, see [Generate Tables Using sys.dummy](/generate-tables-using-sys-dummy).

### ACCURACY\_SCORE

Returns the fraction of predictions that match the actual class labels. This function calculates the ratio of correctly predicted rows to the total number of rows.

```sql SQL theme={null}
ACCURACY_SCORE(y, y_hat)
```

| **Argument** | **Data Type**       | **Description**            |
| ------------ | ------------------- | -------------------------- |
| `y`          | All types supported | The actual class label.    |
| `y_hat`      | All types supported | The predicted class label. |

The return type is `DOUBLE`. The result represents the proportion of correct predictions, ranging from 0 to 1.

**Examples**

**Calculate the Accuracy Score for Matched Values**

This query returns the accuracy score when all predictions match the actual values.

```sql SQL theme={null}
SELECT ACCURACY_SCORE(c1, c1) FROM sys.dummy10;
```

Output: `1`

**Calculate the Accuracy Score with Comparison Against Squared Values**

This query returns the accuracy score when comparing actual values against their squared values.

```sql SQL theme={null}
SELECT ACCURACY_SCORE(c1, c1 * c1) FROM sys.dummy10;
```

Output: `0.1`

### ANY\_VALUE

Returns an arbitrary non-`NULL` value from the input column. The function returns `NULL` only if all rows in the column are `NULL`.

The `ANY_VALUE` function does not support window aggregation.

**Syntax**

```sql SQL theme={null}
ANY_VALUE(col)
```

| **Argument** | **Data Type** | **Description**                                                |
| ------------ | ------------- | -------------------------------------------------------------- |
| `col`        | Any type      | The column from which to return an arbitrary non-`NULL` value. |

**Example**

```sql SQL theme={null}
SELECT
    ANY_VALUE(label) AS sample_label
FROM sample_data;
```

Output: `alpha`

<Info>
  The returned value is non-deterministic. The database can return any non-`NULL` value from the column.
</Info>

### APPROX\_COUNT\_DISTINCT

Returns an approximate count of distinct values in the column using the HyperLogLog algorithm, with a 95% confidence interval that the result is within 4.5% of the exact count.

**Syntax**

```sql SQL theme={null}
APPROX_COUNT_DISTINCT(col)
```

| **Argument** | **Data Type**         | **Description**                                         |
| ------------ | --------------------- | ------------------------------------------------------- |
| `col`        | Any non-interval type | The column for the approximation of the distinct count. |

**Example**

```sql SQL theme={null}
SELECT
    APPROX_COUNT_DISTINCT(val) AS approx_distinct
FROM sample_data;
```

Output: `7`

The `sample_data` table has 8 rows, but there are only 7 distinct values in the `val` column (the value `4` appears twice).

### APPROX\_SUM

Computes a sum using a faster, non-deterministic ordering for floating-point columns. This summation can lead to minor differences in the result on the order of the machine epsilon. For integral column types, the function uses the standard `SUM` algorithm.

**Syntax**

```sql SQL theme={null}
APPROX_SUM(col)
```

| **Argument** | **Data Type**                           | **Description**    |
| ------------ | --------------------------------------- | ------------------ |
| `col`        | `FLOAT`, `DOUBLE`, or any integral type | The column to sum. |

<Info>
  For integral inputs, the `APPROX_SUM` function uses the standard `SUM` algorithm and returns the same result. The performance benefit applies to large floating-point data sets.
</Info>

**Example**

```sql SQL theme={null}
SELECT
    APPROX_SUM(c1) AS approx_total
FROM sys.dummy5;
```

Output: `15`

### AVG

Computes the arithmetic mean over the set of values.

**Syntax**

```sql SQL theme={null}
AVG(col)
```

| **Argument** | **Data Type**                | **Description**                                                                                                                                           |
| ------------ | ---------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `col`        | Any numeric type or `MATRIX` | The column to average. <br /><br />Integral and `FLOAT` inputs return `DOUBLE`. <br /><br />`DOUBLE`, `DECIMAL`, and `MATRIX` inputs preserve their type. |

**Example**

```sql SQL theme={null}
SELECT
    AVG(c1) AS avg_val
FROM sys.dummy5;
```

Output: `3.0`

### COEFFICIENT\_OF\_DETERMINATION

Computes the coefficient of determination (R²) between actual and predicted values. Primarily used to evaluate the performance of machine learning regression models, R² measures the proportion of variance in the actual values that the predicted values explain. A value of `1.0` indicates a perfect fit, while `0.0` indicates that the predictions explain none of the variance.

This function returns `NULL` when the total sum of squares is zero (i.e., all actual values are identical).

**Syntax**

```sql SQL theme={null}
COEFFICIENT_OF_DETERMINATION(actual, predicted)
```

| **Argument** | **Data Type**    | **Description**                 |
| ------------ | ---------------- | ------------------------------- |
| `actual`     | Any numeric type | The column of observed values.  |
| `predicted`  | Any numeric type | The column of predicted values. |

**Example**

```sql SQL theme={null}
SELECT
    COEFFICIENT_OF_DETERMINATION(actual, predicted) AS r_squared
FROM predictions;
```

Output: `0.980571428571429`

### CONFUSION\_MATRIX

Returns a structured representation of the counts for every combination of actual and predicted class labels relative to a specified positive class. This function aggregates predictions into a 2x2 matrix in the format `[[TP, FP], [FN, TN]]` for binary classification analysis.

```sql SQL theme={null}
CONFUSION_MATRIX(y, y_hat, positive_class)
```

| **Argument**     | **Data Type**       | **Description**                                                                         |
| ---------------- | ------------------- | --------------------------------------------------------------------------------------- |
| `y`              | All types supported | The actual class label.                                                                 |
| `y_hat`          | All types supported | The predicted class label.                                                              |
| `positive_class` | All types supported | The class label to treat as the positive class. Must match the type of `y` and `y_hat`. |

The return type is `TUPLE`. Each entry in the result contains the count of rows for a specific combination of actual and predicted class values, organized as true positives, false positives, false negatives, and true negatives.

**Examples**

**Calculate the Confusion Matrix with Matched Values**

This query returns the confusion matrix when all predictions match the actual values.

```sql SQL theme={null}
SELECT CONFUSION_MATRIX(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `[[5, 0], [0, 5]]`

**Calculate the Confusion Matrix with Mismatched Classifiers**

This query returns the confusion matrix for two mismatched binary classifiers, where actual positives are rows with `c1 > 5` and predicted positives are rows with `c1 < 3`.

```sql SQL theme={null}
SELECT CONFUSION_MATRIX(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 < 3 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `[[0, 2], [5, 3]]`

### CORR

Alias for [CORRELATIONP](#correlationp).

### CORRELATION

Alias for [CORRELATIONP]().

### CORRELATIONP

Computes the population Pearson correlation coefficient between two columns. Returns `DOUBLE`, or `DECIMAL` if both inputs are `DECIMAL`-compatible.

Alias for `CORRELATION` and `CORR`.

**Syntax**

```sql SQL theme={null}
CORRELATIONP(col1, col2)
```

| **Argument** | **Data Type**    | **Description**    |
| ------------ | ---------------- | ------------------ |
| `col1`       | Any numeric type | The first column.  |
| `col2`       | Any numeric type | The second column. |

**Example**

```sql SQL theme={null}
SELECT
    CORRELATIONP(actual, predicted) AS corr_val
FROM predictions;
```

Output: `0.9904654955172436`

### COUNT

Returns the number of rows in the set where values in `col` are not `NULL`. When you use the `COUNT(*)` SQL statement, the function returns the total number of rows. The return type is `BIGINT`.

**Syntax**

```sql SQL theme={null}
COUNT(col)
```

| **Argument** | **Data Type**         | **Description**                                                                           |
| ------------ | --------------------- | ----------------------------------------------------------------------------------------- |
| `col`        | Any non-interval type | The column for counting non-`NULL` values. <br /><br />Use `*` to count all rows instead. |

**Examples**

**Count All Rows**

```sql SQL theme={null}
SELECT COUNT(*) AS total_rows FROM sample_data;
```

Output: `8`

**Count Non-NULL Values**

The `label` column has one `NULL` row.

```sql SQL theme={null}
SELECT COUNT(label) AS rows_with_label FROM sample_data;
```

Output: `7`

### COVAR\_POP

Alias for [COVARIANCEP](#covariancep).

### COVAR\_SAMP

Alias for [COVARIANCE]().

### COVARIANCE

Computes the sample covariance between two columns. Returns `DOUBLE` by default, or `DECIMAL` if both inputs are `DECIMAL`-compatible. `MATRIX` inputs preserve their type.

Alias for `COVAR_SAMP`.

**Syntax**

```sql SQL theme={null}
COVARIANCE(col1, col2)
```

| **Argument** | **Data Type**                         | **Description**    |
| ------------ | ------------------------------------- | ------------------ |
| `col1`       | Any numeric type or `MATRIX` (square) | The first column.  |
| `col2`       | Any numeric type or `MATRIX` (square) | The second column. |

<Info>
  Matrix arguments must have matching dimensions.
</Info>

**Example**

```sql SQL theme={null}
SELECT
    COVARIANCE(actual, predicted) AS cov_samp
FROM predictions;
```

Output: `336.0`

### COVARIANCEP

Computes the population covariance between two columns. Returns `DOUBLE` by default, or `DECIMAL` if both inputs are compatible with `DECIMAL`.

`MATRIX` inputs preserve their type.

Alias for `COVAR_POP`.

**Syntax**

```sql SQL theme={null}
COVARIANCEP(col1, col2)
```

| **Argument** | **Data Type**                         | **Description**    |
| ------------ | ------------------------------------- | ------------------ |
| `col1`       | Any numeric type or `MATRIX` (square) | The first column.  |
| `col2`       | Any numeric type or `MATRIX` (square) | The second column. |

<Info>
  Matrix arguments must have matching dimensions.
</Info>

**Example**

```sql SQL theme={null}
SELECT
    COVARIANCEP(actual, predicted) AS cov_pop
FROM predictions;
```

Output: `280.0`

### F1\_SCORE

Returns the harmonic mean of precision and recall for a specified positive class. This function calculates `2 * (Precision * Recall) / (Precision + Recall)` and provides a single score that balances both precision and recall. The function returns `NULL` when both precision and recall are 0.

```sql SQL theme={null}
F1_SCORE(y, y_hat, positive_class)
```

| **Argument**     | **Data Type**       | **Description**                                                                         |
| ---------------- | ------------------- | --------------------------------------------------------------------------------------- |
| `y`              | All types supported | The actual class label.                                                                 |
| `y_hat`          | All types supported | The predicted class label.                                                              |
| `positive_class` | All types supported | The class label to treat as the positive class. Must match the type of `y` and `y_hat`. |

The return type is `DOUBLE`. The result ranges from 0 to 1, where 1 represents perfect precision and recall.

**Examples**

**Calculate the F1 Score with Matching Values**

This query returns the F1 score when all predictions match the actual values.

```sql SQL theme={null}
SELECT F1_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `1`

**Calculate the F1 Score for Mismatched Classifiers**

This query returns `NULL` because both precision and recall are 0 for the mismatched classifiers.

```sql SQL theme={null}
SELECT F1_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 < 3 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `NULL`

### KURTOSIS

Computes the sample excess kurtosis over the set of values. Kurtosis measures the difference between the tails of a distribution and the tails of a normal distribution. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

**Syntax**

```sql SQL theme={null}
KURTOSIS(col)
```

| **Argument** | **Data Type**    | **Description**                               |
| ------------ | ---------------- | --------------------------------------------- |
| `col`        | Any numeric type | The column for computing the sample kurtosis. |

**Example**

```sql SQL theme={null}
SELECT
    KURTOSIS(val) AS kurt_val
FROM sample_data;
```

Output: `4.763725276787391`

### KURTOSISP

Computes the population kurtosis over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

**Syntax**

```sql SQL theme={null}
KURTOSISP(col)
```

| **Argument** | **Data Type**    | **Description**                               |
| ------------ | ---------------- | --------------------------------------------- |
| `col`        | Any numeric type | The column for computing population kurtosis. |

**Example**

```sql SQL theme={null}
SELECT
    KURTOSISP(val) AS kurtp_val
FROM sample_data;
```

Output: `2.268440607993995`

### MAX

Returns the maximum value in the specified column. The return type matches the input.

**Syntax**

```sql SQL theme={null}
MAX(col)
```

| **Argument** | **Data Type**         | **Description**                             |
| ------------ | --------------------- | ------------------------------------------- |
| `col`        | Any non-interval type | The column for returning the maximum value. |

**Example**

```sql SQL theme={null}
SELECT
    MAX(c1) AS max_val
FROM sys.dummy5;
```

Output: `5`

### MEAN\_ABSOLUTE\_ERROR

Returns the mean absolute error (MAE) between actual and predicted values. This function calculates the average of the absolute differences between each pair of actual and predicted values, treating all errors equally regardless of direction.

```sql SQL theme={null}
MEAN_ABSOLUTE_ERROR(y, y_hat)
```

| **Argument** | **Data Type** | **Description**      |
| ------------ | ------------- | -------------------- |
| `y`          | NUMERIC       | The actual value.    |
| `y_hat`      | NUMERIC       | The predicted value. |

The return type is `DOUBLE`. The result is always zero or positive. A value of 0 indicates perfect predictions.

Examples

**Calculate the MAE for Matched Values**

This query returns the mean absolute error when all predictions match the actual values.

```sql SQL theme={null}
SELECT MEAN_ABSOLUTE_ERROR(c1, c1) FROM sys.dummy10;
```

Output: `0`

**Calculate the MAE with Comparison Against Squared Values**

This query returns the mean absolute error when comparing actual values against their squared values.

```sql SQL theme={null}
SELECT MEAN_ABSOLUTE_ERROR(c1, c1 * c1) FROM sys.dummy10;
```

Output: `33.0`

### MEAN\_ABSOLUTE\_PERCENTAGE\_ERROR

Returns the mean absolute percentage error (MAPE) between actual and predicted values. This function calculates the average of the absolute percentage differences using the formula `Average( AbsoluteValue( (y - y_hat) / y ) ) * 100`, which expresses the error relative to the actual values.

```sql SQL theme={null}
MEAN_ABSOLUTE_PERCENTAGE_ERROR(y, y_hat)
```

| **Argument** | **Data Type** | **Description**                                                                                       |
| ------------ | ------------- | ----------------------------------------------------------------------------------------------------- |
| `y`          | NUMERIC       | The actual value. Must not be 0, because this value is the denominator in the percentage calculation. |
| `y_hat`      | NUMERIC       | The predicted value.                                                                                  |

The return type is `DOUBLE`. The result is a percentage value where 0 indicates perfect predictions.

Examples

**Calculate the MAPE for Matched Values**

This query returns the mean absolute percentage error when all predictions match the actual values.

```sql SQL theme={null}
SELECT MEAN_ABSOLUTE_PERCENTAGE_ERROR(c1, c1) FROM sys.dummy10;
```

Output: `0`

**Calculate the MAPE with Comparison Against Squared Values**

This query returns the mean absolute percentage error when comparing actual values against their squared values.

```sql SQL theme={null}
SELECT MEAN_ABSOLUTE_PERCENTAGE_ERROR(c1, c1 * c1)
    FROM sys.dummy10;
```

Output: `450.0`

### MIN

Returns the minimum value in the specified column. The return type matches the input.

**Syntax**

```sql SQL theme={null}
MIN(col)
```

| **Argument** | **Data Type**         | **Description**                             |
| ------------ | --------------------- | ------------------------------------------- |
| `col`        | Any non-interval type | The column for returning the minimum value. |

**Example**

```sql SQL theme={null}
SELECT
    MIN(c1) AS min_val
FROM sys.dummy5;
```

Output: `1`

### PRECISION\_SCORE

Returns the precision score for a specified positive class. This function calculates the ratio of true positives to the total number of predicted positives using the formula `True Positives / (True Positives + False Positives)`. Of all the predictions for the specified class, this score measures how many are correct.

```sql SQL theme={null}
PRECISION_SCORE(y, y_hat, positive_class)
```

| **Argument**     | **Data Type**       | **Description**                                                                         |
| ---------------- | ------------------- | --------------------------------------------------------------------------------------- |
| `y`              | All types supported | The actual class label.                                                                 |
| `y_hat`          | All types supported | The predicted class label.                                                              |
| `positive_class` | All types supported | The class label to treat as the positive class. Must match the type of `y` and `y_hat`. |

The return type is `DOUBLE`. The result ranges from 0 to 1, where 1 indicates that every prediction for the positive class is correct.

**Examples**

**Calculate the Precision Score for Matched Values**

This query returns the precision score when all predictions match the actual values.

```sql SQL theme={null}
SELECT PRECISION_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `1`

**Calculate the Precision Score for Mismatched Classifiers**

This query returns the precision score for two mismatched binary classifiers, where none of the predicted positives are actual positives.

```sql SQL theme={null}
SELECT PRECISION_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 < 3 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `0.0`

### PRODUCT

Computes the product of all values in the column.

**Syntax**

```sql SQL theme={null}
PRODUCT(col)
```

| **Argument** | **Data Type**                                                         | **Description**                                          |
| ------------ | --------------------------------------------------------------------- | -------------------------------------------------------- |
| `col`        | `FLOAT`, `DOUBLE`, `DECIMAL`, any integral type, or `MATRIX` (square) | The column to multiply. Integral inputs return `BIGINT`. |

<Info>
  Matrix arguments must have matching dimensions.
</Info>

**Example**

```sql SQL theme={null}
SELECT
    PRODUCT(c1) AS factorial_5
FROM sys.dummy5;
```

Output: `120`

### RECALL\_SCORE

Returns the recall score for a specified positive class. This function calculates the ratio of true positives to the total number of actual positives using the formula `True Positives / (True Positives + False Negatives)`. Of all the actual instances of the specified class, this score measures how many the model correctly identifies.

```sql SQL theme={null}
RECALL_SCORE(y, y_hat, positive_class)
```

| **Argument**     | **Data Type**       | **Description**                                                                         |
| ---------------- | ------------------- | --------------------------------------------------------------------------------------- |
| `y`              | All types supported | The actual class label.                                                                 |
| `y_hat`          | All types supported | The predicted class label.                                                              |
| `positive_class` | All types supported | The class label to treat as the positive class. Must match the type of `y` and `y_hat`. |

The return type is `DOUBLE`. The result ranges from 0 to 1, where 1 indicates that every actual instance of the positive class is correctly identified.

**Examples**

**Calculate the Recall Score for Matched Values**

This query returns the recall score when all predictions match the actual values.

```sql SQL theme={null}
SELECT RECALL_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `1`

**Calculate the Recall Score for Mismatched Classifiers**

This query returns the recall score for two mismatched binary classifiers, where none of the actual positives are correctly predicted.

```sql SQL theme={null}
SELECT RECALL_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    CASE WHEN c1 < 3 THEN 1 ELSE 0 END,
    1
) FROM sys.dummy10;
```

Output: `0.0`

### ROC\_AUC\_SCORE

Returns the area under the receiver operating characteristic (ROC) curve (AUC). This function measures the ability of a binary classification model to distinguish between classes. A score of 1.0 represents a perfect classifier, while a score of 0.5 represents performance no better than random guessing.

```sql SQL theme={null}
ROC_AUC_SCORE(y_true, y_score, positive_class)
```

| **Argument**     | **Data Type**      | **Description**                                                                                                                                            |
| ---------------- | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `y_true`         | BOOLEAN or INTEGER | The true binary class label, where the values represent two classes (for example, 0 or 1).                                                                 |
| `y_score`        | NUMERIC            | The predicted probability or confidence score for the positive class. A score of 1.0 is a perfect classifier, while 0.5 is no better than random guessing. |
| `positive_class` |                    | The class label to treat as the positive class. Must match the type of `y_true`.                                                                           |

The return type is `DOUBLE`. The result ranges from 0 to 1.

**Example**

This query returns the AUC score for a classifier in which higher probability scores correspond to actual positives, indicating perfect class separation.

```sql SQL theme={null}
SELECT ROC_AUC_SCORE(
    CASE WHEN c1 > 5 THEN 1 ELSE 0 END,
    DOUBLE(c1) / 10,
    1
) FROM sys.dummy10;
```

Output: `1.0`

### SKEW

Computes the sample skewness over the set of values. Skewness measures the asymmetry of a distribution. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

**Syntax**

```sql SQL theme={null}
SKEW(col)
```

| **Argument** | **Data Type**    | **Description**                               |
| ------------ | ---------------- | --------------------------------------------- |
| `col`        | Any numeric type | The column for computing the sample skewness. |

**Example**

```sql SQL theme={null}
SELECT
    SKEW(val) AS skew_val
FROM sample_data;
```

Output: `0.8804164883619399`

### SKEWP

Computes the population skewness over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

**Syntax**

```sql SQL theme={null}
SKEWP(col)
```

| **Argument** | **Data Type**    | **Description**                                   |
| ------------ | ---------------- | ------------------------------------------------- |
| `col`        | Any numeric type | The column for computing the population skewness. |

**Example**

```sql SQL theme={null}
SELECT
    SKEWP(val) AS skewp_val
FROM sample_data;
```

Output: `0.7059036122393627`

### STDEV

Computes the sample standard deviation over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

Alias for `STDDEV` and `STDDEV_SAMP`.

**Syntax**

```sql SQL theme={null}
STDEV(col)
```

| **Argument** | **Data Type**    | **Description**                                         |
| ------------ | ---------------- | ------------------------------------------------------- |
| `col`        | Any numeric type | The column for computing the sample standard deviation. |

**Example**

```sql SQL theme={null}
SELECT
    STDEV(val) AS stdev_val
FROM sample_data;
```

Output: `4.421942042651783`

### STDDEV

Alias for [STDEV](#stdev).

### STDDEV\_POP

Alias for [STDEVP](#stdevp).

### STDDEV\_SAMP

Alias for [STDEV](#stdev).

### STDEVP

Computes the population standard deviation over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

Alias for `STDDEV_POP`.

**Syntax**

```sql SQL theme={null}
STDEVP(col)
```

| **Argument** | **Data Type**    | **Description**                                             |
| ------------ | ---------------- | ----------------------------------------------------------- |
| `col`        | Any numeric type | The column for computing the population standard deviation. |

**Example**

```sql SQL theme={null}
SELECT
    STDEVP(val) AS stdevp_val
FROM sample_data;
```

Output: `4.136348026943574`

### SUM

Computes the sum of all values in the column.

**Syntax**

```sql SQL theme={null}
SUM(col)
```

| **Argument** | **Data Type**                | **Description**                                                                                                                                                                       |
| ------------ | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `col`        | Any numeric type or `MATRIX` | The column to sum. <br /><br />Integral inputs return `BIGINT`. <br />d<br />`FLOAT` inputs return `FLOAT`. <br /><br />`DOUBLE`, `DECIMAL`, and `MATRIX` inputs preserve their type. |

**Example**

```sql SQL theme={null}
SELECT
    SUM(c1) AS total
FROM sys.dummy5;
```

Output: `15`

### VAR\_POP

Alias for [VARIANCEP](#variancep).

### VAR\_SAMP

Alias for [VARIANCE](#variance).

### VARIANCE

Computes the sample variance over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

`MATRIX` inputs preserve their type and must be square.

Alias for `VAR_SAMP`.

**Syntax**

```sql SQL theme={null}
VARIANCE(col)
```

| **Argument** | **Data Type**                         | **Description**                               |
| ------------ | ------------------------------------- | --------------------------------------------- |
| `col`        | Any numeric type or `MATRIX` (square) | The column for computing the sample variance. |

<Info>
  Matrix arguments must have matching dimensions.
</Info>

**Example**

```sql SQL theme={null}
SELECT
    VARIANCE(val) AS var_val
FROM sample_data;
```

Output: `19.553571428571427`

### VARIANCEP

Computes the population variance over the set of values. Returns `DOUBLE`, or `DECIMAL` if the input is `DECIMAL`.

`MATRIX` inputs preserve their type and must be square.

Alias for `VAR_POP`.

**Syntax**

```sql SQL theme={null}
VARIANCEP(col)
```

| **Argument** | **Data Type**                         | **Description**                               |
| ------------ | ------------------------------------- | --------------------------------------------- |
| `col`        | Any numeric type or `MATRIX` (square) | The column for computing population variance. |

**Example**

```sql SQL theme={null}
SELECT
    VARIANCEP(val) AS varp_val
FROM sample_data;
```

Output: `17.109375`

## Sorted Aggregate Functions

The general syntax for sorted aggregate functions adds the DISTINCT and ORDER BY keywords in the function invocation.

```sql SQL theme={null}
AGGREGATE([DISTINCT] arg1, arg2, ... [ORDER BY ...])
```

### ARRAY\_AGG

Returns an array containing every row from the expression.

**Syntax**

```sql SQL theme={null}
ARRAY_AGG(expr)
```

| **Argument** | **Data Type**    | **Description**                           |
| ------------ | ---------------- | ----------------------------------------- |
| `expr`       | Any numeric type | Expression for aggregation into an array. |

**Example**

Aggregate ten rows into an array in descending order.

```sql SQL theme={null}
SELECT
    ARRAY_AGG(c1 ORDER BY c1 DESC)
FROM sys.dummy10;
```

Output: `[10,9,8,7,6,5,4,3,2,1]`

### ARRAY\_CONCAT\_AGG

Returns an array that concatenates arrays across rows. The input argument is a SQL expression. The element type must be consistent across rows. The function ignores NULL inputs and returns NULL only when all inputs are NULL.

The `DISTINCT` keyword removes duplicate arrays. To remove duplicate elements from an array, use the `ARRAY_DISTINCT` function during load.

The `ORDER BY` syntax controls the order of concatenation across rows. To sort elements in the array, use the `ARRAY_SORT` function during load.

**Syntax**

```sql SQL theme={null}
ARRAY_CONCAT_AGG(expr)
```

| **Argument** | **Data Type**  | **Description**                           |
| ------------ | -------------- | ----------------------------------------- |
| `expr`       | SQL expression | Expression for concatenation across rows. |

**Example**

Create the `data_type_example` table with a column that stores arrays of integers.

```sql SQL theme={null}
CREATE TABLE
    data_type_example (col_int_array INT[] NOT NULL DEFAULT 'INT[0,1,2,3]');
```

Insert one array with values `1`, `2`, and `3` into the table.

```sql SQL theme={null}
INSERT INTO data_type_example SELECT ARRAY[1,2,3];
```

Insert the second array with values `4`, `5`, and `6` into the table.

```sql SQL theme={null}
INSERT INTO data_type_example SELECT ARRAY[4,5,6];
```

Concatenate the two arrays into one array in ascending order.

```sql SQL theme={null}
SELECT ARRAY_CONCAT_AGG(col_int_array ORDER BY col_int_array ASC)
FROM data_type_example;
```

Output: `[1,2,3,4,5,6]`

### STRING\_AGG

Returns a string concatenated from every row of the expression.

**Syntax**

```sql SQL theme={null}
ARRAY_CONCAT_AGG(expr, delimiter)
```

| **Argument** | **Data Type**  | **Description**                           |
| ------------ | -------------- | ----------------------------------------- |
| `expr`       | SQL expression | Expression for concatenation across rows. |
| `delimiter`  |                | Optional.<br />                           |

**Examples**

**Concatenate Rows as a String**

Concatenate ten rows into a string in descending order. Cast integers into strings using the `CHAR` casting function.

```sql SQL theme={null}
SELECT
    STRING_AGG(char(c1) ORDER BY c1 DESC)
FROM sys.dummy10;
```

Output: `"10987654321"`

**Concatenate Rows as a String with a Delimiter**

Concatenate ten rows into a string in ascending order. Cast integers into strings using the `CHAR` casting function. Use the `|` delimiter.

```sql SQL theme={null}
SELECT
    STRING_AGG(char(c1), '|' ORDER BY c1 ASC)
FROM sys.dummy10;
```

Output: `"1|2|3|4|5|6|7|8|9|10"`

## Related Links

[Math Functions and Operators](/math-functions-and-operators)

[Query Ocient](/query-ocient)