Understanding Query Totals and Grouping

In a query, you use a totals method to perform 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 following table shows the available totals methods.

Value	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. This means that result is not 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.

Working with Totals and Grouping in Query Designer

To specify a totals method in Query Designer, you set the Method Type column to Totals, and then select the totals method in Method column. By default, retrieved records are automatically grouped by the other columns in the query, as indicated by a check mark in the Group By column in Query Designer. When grouped by a column, records that have similar values for the column are grouped together and the totals method is applied against the records in the group. A summary value is calculated and returned in a single row for the group in the dataset.

Note

Except for the Count method, you can only use a totals method (Sum, Average, Min, and Max) on a field that has a numeric data type of Decimal, Integer, BigInteger, or Duration.

The following illustration shows how to use Query Designer to create a query that links the Customer table and the Sales Line table and retrieves the total number of items for each customer. The query is grouped by the No. and Name columns.

The totals methods and grouping correspond to using aggregate functions and the GROUP BY clause, respectively, in SQL SELECT statements. For more information, see Creating Queries with Totals in SQL.

The grouping concept is further explained in the examples for each totals method in the following sections.

Sample Query

The sample query retrieves the quantity of items and customer for each open sales order from the CRONUS International Ltd. demonstration database. The query links table 18 Customer with table 37 Sales Line. The following illustration shows the query setup in Query Designer.

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

Customer number	Customer name	Quantity
20000	Selangorian Ltd.	200
30000	Blanemark Hifi	150
20000	Selangorian Ltd.	300
40000	Deerfield Graphics	250
20000	Selangorian Ltd.	400
30000	Blanemark Hifi	350

In its current state, the query does not implement a totals method. The following sections explain how you can modify the query to implement the different totals methods.

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 MethodType property to Totals and the Method property to Sum. The name of the Quantity column automatically changes to Sum_Quantity and the query is automatically grouped by the No. and Name columns. The following illustration shows the query in Query Designer.

The following table illustrates the resulting dataset for the query.

Customer number	Customer name	Quantity
20000	Selangorian Ltd.	900
30000	Blanemark Hifi	500
40000	Deerfield Graphics	250

For step-by-step instructions about how to create this query, see Walkthrough: Creating a Query That Uses a Totaling Method and Sorting.

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 MethodType property to Totals and the Method property to Sum. The name of the Quantity column automatically changes to Ave_Quantity and the query is automatically grouped by the No. and Name columns.

The following table illustrates the resulting dataset for the query.

Customer number	Customer name	Quantity
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 MethodType property to Totals and 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.

The following table illustrates the resulting dataset for the query.

Customer number	Customer name	Quantity
20000	Selangorian Ltd.	200
30000	Blanemark Hifi	150
40000	Deerfield Graphics	250

Max

The Min method retrieves the highest 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 MethodType property to Totals and 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.

The following table illustrates the resulting dataset for the query.

Customer number	Customer name	Quantity
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 totals 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, do the following:

1. Delete the Quantity column because the Count method is not associated with a specific column.

   Note

   When using the Count method, the DataSource Property must be blank.

2. On a blank line under the data item for Sales Lines, set the MethodType property to Totals and the Method property to Count. The name of the new column automatically changes to Count_ and the query is automatically grouped by the No. and Name columns.

   Query Designer will look similar to the following illustration.

   

The following table illustrates the resulting dataset for the query.

Customer number	Customer name	Count
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 Totals in SQL

If you are familiar with SQL, then it is helpful to know how the totaling methods in Microsoft Dynamics NAV relate to SQL statements. To specify a totaling method in an SQL statement, you add the method to the SELECT statement and then add a GROUP BY clause. To group results on columns, you add the GROUPED BY statement.

The following example shows how to use an SQL statement to create an inner join of the Customer table and the 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

Tasks

Walkthrough: Creating a Query That Uses a Totaling Method and Sorting

Reference

Method Property