# Aggregates

#### Warning

This schema section is obsolete and will be removed in the future.

Use grouping for fetching aggregated data. See Grouping for more information.

Business NXT GraphQL supports computing aggregate values for table fields. Aggregate functions ignore null values in the tables. These are deterministic and return the same value each time that they are called. They are similar to the aggregate functions available in SQL. The supported aggregates are listed in the following table. The returned values are computed from the selection determined by provided filter. The filter is optional and if not specified all the records in the table are included.

Aggregate | Column types | Description |
---|---|---|

`sum` |
numerical | The sum of all the values. |

`sumDistinct` |
numerical | The sum of all distinct values. |

`average` |
numerical | The average of all the values. |

`averageDistinct` |
numerical | The averate of all distinct values. |

`count` |
numerical | The number of the items. |

`countDistinct` |
numerical | The number of distinct items. |

`minimum` |
numerical, date, time | The minimum value. |

`maximum` |
numerical, date, time | The maximum value. |

`variance` |
numerical | The statistical variance of all the values. |

`varianceDistinct` |
numerical | The statistical variance of all the distinct values. |

`variancePopulation` |
numerical | The statistical variance for the population of all the values. |

`variancePopulationDistinct` |
numerical | The statistical variance for the population of all the distinct values. |

`standardDeviation` |
numerical | The statistical standard deviation of all the values. |

`standardDeviationDistinct` |
numerical | The statistical standard deviation of all the distinct values. |

`standardDeviationPopulation` |
numerical | The statistical standard deviation for the population of all the values. |

`standardDeviationPopulationDistinct` |
numerical | The statistical standard deviation for the population of all the distinct values. |

Aggregates can be computed on all the tables that can be querried from GraphQL. Every field in the schema used to query a table (having the same name as the table) has a companion field used to execute aggregate functions. This field has the name format `<table>_aggregate`

. For example, for reading data from the orders table, there is a field called `order`

and for reading aggregate values there is a field called `order_aggregate`

.

The `<table>_aggregate`

field has a query argument that represents a filter. This is the same filter object used to query data from the table. This is documented in the Filtering page.

The type of the `<table>_aggregate`

field has the name format `Query_<parent>_<table>Aggregate_Node`

. For instance, for the `order_aggregate`

field the type name is `Query_UseCompany_OrderAggregate_Node`

. This type contains one field for each aggregate function listed in the above table. This can be seen in the GraphiQL document explorer as follows:

The `minimum`

and `maximum`

fields are of a type with the name format `<table>WithDateTimeAggregate`

. This type includes fields that represent dates and times. The others fields have a different the type that only contains numerical columns. This type has the name format `<table>Aggregate`

. For instance, for the `Order`

table, the type that includes dates and times is called `OrderWithDateTimeAggregate`

, and the type that only includes numerical fields is called `OrderAggregate`

.

Only numerical fields | Numerical + Date/Time fields |
---|---|

Executing aggregate functions is demonstrated with the following example:

```
query read($cid : Int!)
{
useCompany(no: $cid)
{
order_aggregate
{
count
{
orderNo
}
sum
{
orderSumNetDomestic
vatAmountDomestic
}
average
{
orderSumNetDomestic
vatAmountDomestic
}
minimum
{
orderDate
orderSumNetDomestic
vatAmountDomestic
}
maximum
{
orderDate
orderSumNetDomestic
vatAmountInCurrency
}
variance
{
orderSumNetDomestic
vatAmountDomestic
}
}
}
}
```

```
{
"data": {
"useCompany": {
"order_aggregate": {
"count": {
"orderNo": 344
},
"sum": {
"orderSumNetDomestic": 26930,
"vatAmountDomestic": 7952.5
},
"average": {
"orderSumNetDomestic": 73.174085,
"vatAmountDomestic": 19.363742
},
"minimum": {
"orderDate": 20130105,
"orderSumNetDomestic": 0,
"vatAmountDomestic": 0
},
"maximum": {
"orderDate": 20151205,
"orderSumNetDomestic": 21950,
"vatAmountInCurrency": 5487.5
},
"variance": {
"orderSumNetDomestic": 1529866.15103014,
"vatAmountDomestic": 93992.56825201
}
}
}
}
}
```

To include a filter, you need to specify it as an argument to the `order_aggregate`

field, as shown here:

```
query read($cid : Int!)
{
useCompany(no: $cid)
{
order_aggregate(filter :
{
orderDate : {_gt : 20140101}
})
{
count
{
orderNo
}
sum
{
orderSumNetDomestic
vatAmountDomestic
}
average
{
orderSumNetDomestic
vatAmountDomestic
}
minimum
{
orderDate
orderSumNetDomestic
vatAmountDomestic
}
maximum
{
orderDate
orderSumNetDomestic
vatAmountInCurrency
}
variance
{
orderSumNetDomestic
vatAmountDomestic
}
}
}
}
```

```
{
"data": {
"useCompany": {
"order_aggregate": {
"count": {
"orderNo": 312
},
"sum": {
"orderSumNetDomestic": 25127,
"vatAmountDomestic": 6642.5
},
"average": {
"orderSumNetDomestic": 82.553134,
"vatAmountDomestic": 21.453173
},
"minimum": {
"orderDate": 20140102,
"orderSumNetDomestic": 0,
"vatAmountDomestic": 0
},
"maximum": {
"orderDate": 20151205,
"orderSumNetDomestic": 21145,
"vatAmountInCurrency": 5675.5
},
"variance": {
"orderSumNetDomestic": 1542331.617824,
"vatAmountDomestic": 95752.0148624473
}
}
}
}
}
```