Creating aggregate queries
Aggregate queries are used to group multiple rows of data based on certain criteria and calculate a single meaningful value from the grouped data. Common aggregate functions include average, sum, count, and others. These queries are frequently used to retrieve data for key performance indicators (KPI) cards, bar charts, and line charts.
About this task
While typical queries return a value or multiple values across rows based on given criteria, aggregate queries provide a single value or grouped values. For instance, to find the count of delayed order lines, a simple query is sufficient. However, to calculate the total value of goods that are associated with these delayed order lines, an aggregate function such as sum is necessary because it combines values across multiple rows into a single result.
Before you begin
Procedure
- In the Code editor, write an aggregation function that is similar to the functions in the KPI card example and Bar chart example.
- Run the query.
KPI card example
The following card shows the total value of order lines with late planned delivery. This card uses a simple query to identify the number of order lines with late planned delivery and uses aggregate function to get the value of order lines with late planned delivery.
query latePlannedDeliveryMetrics($tenantId: String, $dateRangeStart: String = "1970-01-01T00:00:00Z", $dateRangeEnd: String = "2970-01-01T00:00:00Z", $categoryFilter: BooleanExp = {CONSTANT_VALUE: true}, $supplierFilter: BooleanExp = {CONSTANT_VALUE: true}, $locationFilter: BooleanExp = {CONSTANT_VALUE: true}) {
totalLatePlannedDelivery: businessObjects(
advancedFilter: {AND: [{GREATER_THAN: [{SELECT: "plannedDeliveryDelay", type: FLOAT}, {VALUE: "0.0", type: FLOAT}]}, {GREATER_EQUALS: [{SELECT: "order.createdDate"}, {VALUE: $dateRangeStart}]}, {LESS_EQUALS: [{SELECT: "order.createdDate"}, {VALUE: $dateRangeEnd}]}, {NOT: {EQUALS: [{SELECT: "deliveryStatusByDate", type: STRING}, {type: STRING, VALUE: "FULLY_DELIVERED"}]}}, {EQUALS: [{SELECT: "order.orderType", type: STRING}, {type: STRING, VALUE: "INBOUND"}]}, $categoryFilter, $supplierFilter, $locationFilter]}
cursorParams: {first: 0}
hint: {viewId: "graph"}
simpleFilter: {tenantId: $tenantId, type: OrderLine}
aggregationParams: {reduce: {expression: {fieldPath: "value", operator: SUM, type: INT}, output: {resultAlias: "totalOrderLinesValue", type: INT}}}
) {
totalCount
aggregationResults {
values {
name
numValue
}
}
edges {
cursor
object {
... on OrderLine {
value
}
}
}
}
}aggregationParams. The key parameters in
aggregationParams are expression, reduce,
fieldPath, and output. - reduce
- Specifies which fields to aggregate and the operation, such as sum, average, or count, to perform on those fields.
- fieldPath
- Specifies the attribute to aggregate. In this example, the attribute is
value. Theoperatordefines the aggregation operation to perform. The values can beCOUNT,COUNT_DISTINCT,SUM,MAX,MIN, orAVG. - output
- Specifies the result or final aggregated value after the defined operation is applied. The name
of the result in this example is
totalOrderLinesValue. - expression
- Includes
fieldpath,operator, andoutput. - aggregationResults
- Defines the result of the aggregation. The values array within
aggregationResultsstores thenameandnumValue. Thenamerefers to the output name of the aggregation, whilenumValuerepresents the aggregated result or value. In this example, the name istotalOrderLinesValueandnumValueis 247170.
Bar chart example
The following chart shows the customers who spent the most money during the defined period. The query of this chart retrieves a list of order lines that are grouped by buyer and aggregated by value. Also, it allows customization of the date range and other parameters by using variables.
query ($dateRangeStart: String = "1970-01-01T00:00:00Z", $dateRangeEnd: String = "2970-01-01T00:00:00Z") {
Query1: businessObjects(
hint: {viewId: "graph"}
cursorParams: {first: 20}
simpleFilter: {type: OrderLine, tenantId: "fcc67ac0-629f-4fbd-a747-1a95885e3c2e"}
advancedFilter: {AND: [{EQUALS: [{SELECT: "order.orderType"}, {VALUE: "OUTBOUND"}]}, {GREATER_EQUALS: [{SELECT: "plannedShipDate"}, {VALUE: $dateRangeStart}]}, {LESS_THAN: [{SELECT: "plannedShipDate"}, {VALUE: $dateRangeEnd}]}]}
aggregationParams: { dimension: {fieldPath: "order.buyer.name", type: STRING}, reduce: {expression: {fieldPath: "value", operator: SUM, type: INT},
output: {resultAlias: "abc", type: INT}},paginationParams: {first: 10, sort: {fieldPath: "abc", order: DESC}}}
) {
aggregationResultsTotalCount
aggregationResults {
dimensionValues {
name
value
__typename
}
values {
name
numValue
__typename
}
__typename
}
__typename
edges {
object {
... on OrderLine {
value
order {
buyer {
name
}
}
}
}
}
}
}The reduce, fieldpath, operator,
output, and expression parameters are similar to the KPI card
example. The following additional parameters are used in this example:
- dimension
- Defines the x-axis, which means it specifies the attribute by which data is grouped. For
example,
order.buyer.name. - PaginationParams
- Defines how the output is visually displayed, which is the number of results that are shown in the chart, typically stored in the list. In this example, the top 10 results are displayed. The output is selected by sorting, which defines the order of the output, which can be ascending or descending.
- aggregationResults
- Defines the result of the aggregation. In this example,
DimensionValuesstores the name of the grouped attribute. For example, F & A Metals and SilverOptics.
What to do next
A course is available to learn more about simple and aggregate queries. For more information, see Supply Chain App Studio: Builders for Dashboards and Workqueues.