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.

System table

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:

System table

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
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
      }
    }
  }
}

Result
{
  "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
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
      }
    }
  }
}

Result
{
  "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
        }
      }
    }
  }
}