# Aggregating data in query objects
In a query object, you can use the properties/devenv-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.<br /><br /> 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<#Minimum>|Retrieves the lowest value of the field in the designated column for all records that are selected as part of the grouped set.
Max<#Maximum>|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. <br /> You can return a set of #using-a-query-to-get-distinct-values values from a table using a Count method.
## 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 methods-auto/decimal/decimal-data-type, methods-auto/integer/integer-data-type, methods-auto/biginteger/biginteger-data-type, or methods-auto/duration/duration-data-type. To set up an aggregate on a column, you set the column's properties/devenv-method-property.
```al
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.
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.
```al
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 properties/devenv-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.
```al
...
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:
```al
...
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:
```al
...
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:
```al
...
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:
```al
...
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.
## Using a query to get distinct values
When working with table data, you might need to get a list of distinct values from the table (in SQL, this would correspond to a SELECT DISTINCT statement). This operation isn't supported out-of-the-box in AL, but you can do it with a query using an aggregation.
This AL example code shows how you can encode distinct into a query using an aggregation.
```al
query 50150 DistinctValuesQuery
{
    Caption = 'My Query';
    QueryType = Normal;

    elements
    {
        dataitem(CustLedgerEntry; "Cust. Ledger Entry")
        {
            column(CustomerNo; "Customer No.")
            {
            }
            column(DocumentType; "Document Type")
            {
            }
            column(Count)
            {
                Method = Count;
            }
        }
    }
}
```
This AL example code shows how you can then query for the distinct values (note that the Count column isn't used here).
```al
trigger OnAction()
var
    MyQuery: Query DistinctValuesQuery;
    CombinationText: Text;
begin
    if MyQuery.Open() then begin
        while MyQuery.Read() do
            CombinationText += MyQuery.CustomerNo + '-' + Format(MyQuery.DocumentType) + '; ';
        MyQuery.Close();
    end;

    Message(CombinationText);
end;
```
## Contributors
This article is maintained by Microsoft. Parts of the examples were originally written by the following contributor.
 Teddy Herryanto<https://www.linkedin.com/in/teddyherryanto/> | Business Central Techno Functional Consultant
## Related information
properties/devenv-method-property
devenv-query-object
Filtering Queries<devenv-query-filters>
Aggregating Data<devenv-query-totals-grouping>
https://learn.microsoft.com/sql/t-sql/functions/aggregate-functions-transact-sql

Copyright (c) Microsoft Corporation. CC BY 4.0, https://creativecommons.org/licenses/by/4.0/; code samples MIT. From https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-totals-grouping, MicrosoftDocs/dynamics365smb-devitpro-pb at 828b6ee (2026-11-19). Changed: includes filled in, links to this site, and for agents shortened without dropping a fact. https://jonas-paprotka.de/docs/dev-itpro/developer/devenv-query-totals-grouping/
