# Aggregate Functions

Learn how to use conversion functions in expressions in ActiveReportsJS.

## Content

To include summary values in a report, you can use aggregate functions described below. All of them have the optional `scope` argument that determines the set of data to use for the calculation. The `scope` argument could have one of the following values.

* The name of a [data set](/activereportsjs/docs/v6.1/ReportAuthorGuide/Databinding#data-set-configuration)
* The name of a [data region](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions)
* The name of a group, such as a [Table Group](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Table#table-grouping)

By default, the value of a `scope` argument is the current scope that depends on a report item that "hosts" the aggregate function. For example, in the [Get Started with Tabular Reports demo](/activereportsjs/demos/Reports/GetStartedWithTabularReports/purejs), the table's [group footer](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Table#group-footer) has the [textbox](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Visualizers/TextBox) that shows the `{Sum(unitsInStock)}` value. This is the call to the `Sum` aggregate function and the `scope` argument is omitted. It means that the calculation scope is the group instance. If a textbox with the same value resides in the [table header](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Table#table-header) instead, the calculation scope is the entire table.

Another optional argument of some aggregate functions is `recursive` that should be set to `Recursive` if you want to display summaries that include values from child groups in a recursive hierarchy. For example, the [Interactive Hierarchy](https://www.grapecity.com/activereportsjs/demos/Reports/InteractiveHierarchy/purejs)demo uses the `{Sum(EmployeeCount, "EntityKey", "Recursive")}` expression to display the cumulative Employee Count for cities, regions, and countries.

### AggregateIf

Returns a conditional aggregated value of values specified by the expression, calculated in the specified scope.

**Syntax**

AggregateIf(`condition`, `aggregate`, `expression`, [`scope`])

**Arguments**

* `condition` \- the expression that evaluates to a boolean value indicating whether to include the `expression` into the calculation
* `aggregate` \- the name of the aggregate function to use for calculation\, for example\, `Count`
* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope

**Examples**

You can use the following expression to return the sum of the `SalesAmount` field values within the current scope. The result only includes SalesAmount values that are greater than 1000.

```js
{AggregateIf(SalesAmount > 1000, "Sum", SalesAmount)}
```

### Avg

Returns the average of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Avg(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the average of `SalesCount` field values within the current scope.

```js
{Avg(SalesCount)}
```

### Count

Returns a count of non-null values specified by the expression, calculated in the specified scope.

**Syntax**

Count(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the number of `UnitsOnOrder` field values within the `ProductsTable` data region.

```js
{Count(UnitsOnOrder, "ProductsTable")}
```

### CountDistinct

Returns a number of distinct non-null values specified by the expression, calculated in the specified scope.

**Syntax**

CountDistinct(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the number of distinct `CustomerId` field values within the current scope.

```js
{CountDistinct(CustomerId)}
```

### CountRows

Returns a number of rows within the specified scope.

**Syntax**

CountRows([`scope`], [`recursive`])

**Arguments**

* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the number of rows within the current scope.

```js
{CountRows()}
```

### DistinctSum

Returns the sum of distinct non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

DistinctSum(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the sum of distinct `Size` field values within the current scope.

```js
{DistinctSum(Size)}
```

### First

Returns the first value of the specified expression, calculated in the specified scope.

**Syntax**

First(`expression`, [`scope`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope

**Examples**

You can use the following expression to return the first value of the `ProductName` field in the `ProductsTable` data region.

```js
{First(ProductName, "ProductsTable")}
```

### Last

Returns the last value of the specified expression, calculated in the specified scope.

**Syntax**

Last(`expression`, [`scope`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope

**Examples**

You can use the following expression to return the last value of the `SalesAmount` field in the current scope.

```js
{Last(SalesAmount)}
```

### Max

Returns the maximum value of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Max(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the maximum value of `Size` field values within the current scope.

```js
{Max(Size)}
```

### Median

Returns the [median](https://en.wikipedia.org/wiki/Median) of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Median(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the median of `SalesAmount` field values within the `Sales` data region.

```js
{Median(SalesAmount, "Sales")}
```

### Min

Returns the minimum value of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Min(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the minimum value of `Size` field values within the current scope.

```js
{Min(Size)}
```

### Mode

Returns the value that appears most often among values specified by the expression, calculated in the specified scope.

**Syntax**

Mode(`expression`, [`scope`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope

**Examples**

You can use the following expression to return the most often value among values of the `ReturnReason` field within the current scope.

```js
{Mode(ReturnReason)}
```

### RunningValue

Returns a running aggregate of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

RunningValue(`expression`, `aggregate`, [`scope`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `aggregate` \- the name of the aggregate function to use for calculation\, for example\, `Count`
* `scope` \- the optional calculation scope

**Examples**

You can use the following expression to return the running value of the sum of the `SalesAmount` field values within the current scope.

```js
{RunningValue(SalesAmount, "Sum")}
```

### StDev

Returns the standard deviation of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

StDev(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the standard deviation of the `ReturnAmount` field values within the current scope.

```js
{StDev(ReturnAmount)}
```

### StDevP

Returns the population standard deviation of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

StDevP(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the population standard deviation of `ReturnAmount` field values within the current scope.

```js
{StDevP(ReturnAmount)}
```

### Sum

Returns the sum of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Sum(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the sum of `SalesAmount` field values within the current scope.

```js
{Sum(SalesAmount)}
```

### Var

Returns the variance of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

Var(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the variance of the `ReturnAmount` field values within the current scope.

```js
{Var(ReturnAmount)}
```

### VarP

Returns the population variance of non-null numeric values specified by the expression, calculated in the specified scope.

**Syntax**

VarP(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the population variance of `ReturnAmount` field values within the current scope.

```js
{VarP(ReturnAmount)}
```

### ToArray

Returns an [Array](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array) containing the values of the specified expression.

**Syntax**

ToArray(`expression`, [`scope`], [`recursive`])

**Arguments**

* `expression` \- the expression on which to calculate the summary value
* `scope` \- the optional calculation scope
* `recursive` \- the optional `Recursive` value indicating whether to perform the calculation recursively

**Examples**

You can use the following expression to return the `ProductName` field values separated by a comma within the current scope.

```js
{Join(ToArray(ProductName), " ,")}
```