In a query, you can use a totals method to perform a calculation on a column and return the calculated value in the resulting dataset. This calculation is frequently called aggregation.
Typically, you would use aggregations with grouping to find totals for specified groups of columns. For example, you can sum the values in the Amount field for each G/L account in the G/L Entry table or find the Average Quantity per item in the Item Ledger Entry table. Totals methods in Business Central queries correspond directly to SQL Server aggregate functions.
You can use the following totals methods to complete specific tasks:
Sum - Calculates the sum of the values of the specified column for all records that are grouped within the dataset.
Average - Calculates the average value in the specified column in a group.
Min - Retrieves the lowest value in the specified column in a group.
Max - Retrieves the highest value in the specified column in a group.
Count - Returns the number of records from data item tables that represent a group in the dataset.
The ensuing code example shows how the Sum method is used in the Method property of the Qty. (Base) field.
query 7345 "Avail Qty. (Base) In QC Bins"
{
Caption = 'Avail Qty. (Base) In QC Bins';
elements
{
dataitem(Location; Location)
{
DataItemTableFilter = "Directed Put-away and Pick" = const(true);
dataitem(Warehouse_Entry; "Warehouse Entry")
{
You can order the data in a resulting dataset of a query by any number of columns that are contained in the query. To define the sorting order, set the OrderBy property on the query. To sort on multiple columns, separate each column with a comma.
virtualization training courses malaysia
DataItemLink = "Location Code" = Location.Code;
SqlJoinType = InnerJoin;
...
column(Sum_Qty_Base; "Qty. (Base)")
{
ColumnFilter = Sum_Qty_Base = filter(> 0);
Method = Sum;
}
...
}
}
}
}
