Aggregating Data in Query Objects

In a query object, you can use the Method property to do a calculation on the fields of a column and return the calculated value in the dataset. For example, you can sum all the fields in a column or find the average value. The Method property is set on column controls and can be set to any of the following aggregate methods.

Aggregate method	Description
Sum	Calculates the sum of the values of the field in the designated column for all records that are selected as part of the grouped set.
Average	Calculates the average value of the field in the designated column for all records that are selected as part of the grouped set. When averaging fields that have an integer data type (such as Integer or BigInteger), integer division is used. The result isn't rounded, and the remainder is discarded. For example, 5÷2=2 instead of 2.5 (or 2 1/2).
Min	Retrieves the lowest value of the field in the designated column for all records that are selected as part of the grouped set.
Max	Retrieves the highest value of the field in the designated column for all records that are selected as part of the grouped set.
Count	Returns the number of records that are selected as part of the grouped set.

Setting up an aggregate method for a query column

Except for the Count method, you can only use an aggregate method (Sum, Average, Min, and Max) on a field that has a numeric data type of Decimal, Integer, BigInteger, or Duration. To set up an aggregate on a column, you set the column's Method Property.

column(Name; Field)
{
    Method = Sum|Average|Min|Max|Count;
}

Setting an aggregate method on a column will automatically group the resultant data set by the other columns in the query. Records that have matching values for the other columns are grouped together into a single row in the results. The aggregate method is then applied against the group and a summary value returned in the row. It's similar to the GROUP BY clause in SQL SELECT statements (see Creating Queries with Aggregates in SQL).

The aggregate methods and grouping are further explained in the following sections.

Sample Query

The following sample query object retrieves the number of line items in every sales order for each customer. The query links the Customer table and the Sales Line table. In its current state, the Method property is commented out so it doesn't implement any aggregate method.

query 50101 "Customer_Sales_Quantity"
{
    QueryType = Normal;
    // Sorts the results in descending order
    OrderBy = descending(Qty);

    elements
    {
        dataitem(C; Customer)
        {
            column(Customer_Number; "No.")
            {
            }

            column(Customer_Name; Name)
            {
            }

            dataitem(SL; "Sales Line")
            {
                DataItemLink = "Sell-to Customer No." = c."No.";
                SqlJoinType = InnerJoin;

                column(Qty; Quantity)
                {
                    // Change the value of the property to perform a different aggregate method on grouped columns:
                    // Sum, Average, Max, Min, or Count
                    // Method = Sum|Average|Min|Max|Count;
                }
            }
        }
    }
}

The following table represents a simplified version of the resulting dataset for the sample query.

Customer_Number	Customer_Name	Qty
20000	Selangorian Ltd.	400
30000	Blanemark Hifi	350
20000	Selangorian Ltd.	300
40000	Deerfield Graphics	250
20000	Selangorian Ltd.	200
30000	Blanemark Hifi	150

The following sections explain how you can modify the query to implement the different aggregate methods by changing the value of the Method property.

Sum

The Sum method adds the values of all fields for the specified column within a group. To set up a Sum method on the Quantity column of the sample query, set the Method property to Sum. The query is automatically grouped by the No. and Name columns.

...
column(Qty; Quantity)
{
    Method = Sum;
}
...

Looking at the sample query, you can use Sum method to get the total number of items in sales orders for each customer. The following table illustrates the resulting dataset for the query.

Customer_Number	Customer_Name	Qty
20000	Selangorian Ltd.	900
30000	Blanemark Hifi	500
40000	Deerfield Graphics	250

Average

The Average method calculates the average value of the fields in the column within a group. To set up an Average method on the Quantity column of the sample query, set the Method property to Average. The query is automatically grouped by the No. and Name columns:

...
column(Qty; Quantity)
{
    Method = Average;
}
...

Looking at the sample query, you can use Average method to get the average number of items in sales orders for each customer. The following table illustrates the resulting dataset for the query.

Customer_Number	Customer_Name	Qty
20000	Selangorian Ltd.	300
30000	Blanemark Hifi	250
40000	Deerfield Graphics	250

Min

The Min method retrieves the lowest value of fields in the column within a group. To set up a Min method on the Quantity column of the sample query, set the Method property to Min. The name of the Quantity column automatically changes to Min_Quantity and the query is automatically grouped by the No. and Name columns:

...
column(Qty; Quantity)
{
    Method = Min;
}
...

Looking at the sample query, you can use Min method to get the least number of items in sales orders for each customer. The following table illustrates the resulting dataset for the query.

Customer_Number	Customer_Name	Qty
40000	Deerfield Graphics	250
20000	Selangorian Ltd.	200
30000	Blanemark Hifi	150

Max

The Max method retrieves the highest value of fields in the column within a group. To set up a Max method on the Quantity column of the sample query, set the Method property to Max. The name of the Quantity column automatically changes to Max_Quantity and the query is automatically grouped by the No. and Name columns:

...
column(Qty; Quantity)
{
    Method = Max;
}
...

Looking at the sample query, you can use Max method to get the greatest number of items in sales orders for each customer. The following table illustrates the resulting dataset for the query.

Customer_Number	Customer_Name	Qty
20000	Selangorian Ltd.	400
30000	Blanemark Hifi	350
40000	Deerfield Graphics	250

Count

The Count method returns the number of records from the data item table that comprise a group in the dataset. Unlike the other aggregate methods, the Count method is not associated with a specific column. Records are identified and counted based on the primary key of the data item table. Referring to the sample query, you can use a Count method to get the number of open sales orders per customer.

To set up a Count method in the sample query, the column element definition cannot include a source table; only a name. Therefore, you can delete the reference to the Quantity field in the column(Qty; Quantity) element and set the Methodproperty to Count:

...
column(Qty)
{
    Method = Count;
}
  

Looking at the sample query, you can use Count method the number of sales orders for each customer. The following table illustrates the resulting dataset for the query.

Customer_Number	Customer_Name	Qty
20000	Selangorian Ltd.	3
30000	Blanemark Hifi	2
40000	Deerfield Graphics	1

In SQL SELECT statements, the Count method corresponds to a COUNT(*) or COUNT(field) clause.

Creating queries with aggregates in SQL

If you're familiar with SQL, then it's helpful to know how the aggregate methods in Business Central relate to SQL statements. To specify an aggregate method in an SQL statement, you add the method to the SELECT statement and then add a GROUP BY clause.

The following example shows how to use an SQL statement to create an inner join of the Customer table and Sales Line table, and a sum of items for each customer. The result is grouped by the No. and Name columns.

SELECT Customer."No.", Customer.Name, SUM("Sales Line".Quantity)  
FROM Customer INNER JOIN "Sales Line"  
  ON Customer."No." = "Sales Line"."Sell-to Customer No."  
GROUP BY Customer."No.", Customer.Name  

See Also

Method Property
Query Object
Filtering Queries
Aggregating Data
Aggregate Functions (Transact-SQL)