Creating GROUP BY clauses in the SQL Query Builder
The GROUP BY clause contains a grouping expression that is used to define the grouping of the result set of the previous clause of the SELECT statement. A GROUP BY clause creates a set of rows. Each row in the results table represents the set of rows for which the grouping expression is equal.
Before you begin
SQL support for grouping in the SQL Query Builder is dependent on the level of support that is provided by IMS™. If you use the SQL Query Builder to create the GROUP BY clause, the tool attempts to create the correct syntax for IMS.
See the documentation for your database product for more information about grouping.
The SELECT statement must be open in the SQL Query Builder.
Procedure
To create a GROUP BY clause in the SQL Query Builder:
- In the Design pane of the SQL Query Builder, click the Groups tab.
- On the Groups page, click Grouping root in the list. The list shows the grouping expression that is currently defined. Initially, this list contains only Grouping root.
- To add to the Grouping root, double-click the first empty row under Column.
- In the cell, select a column, build an expression, or select a grouping type from the list. Selecting a column or specifying an expression defines the aspect of the result set that is evaluated to determine the grouping. By selecting a grouping type, you are nesting a grouping clause.
- To modify a nested grouping type, select the type in the grouping expression list, and then complete the details for that grouping type. You can add columns, expressions, and groups to the grouping type in the Columns list