Applying Group By Clause in Composite Queries

The Group By column is added automatically when you apply the first aggregate function to the output field. By default, all non-aggregate output fields are included in the initial Group By clause in the same order as in the output field list. You use the Edit Group By page (PSCPQGROUPBY_SEC) to edit the order of the Group By fields.

Image: Edit Group By page

This example illustrates the fields and controls on the Edit Group By page.

To edit the Group By list:

1. Select Reporting Tools, Composite Query, Composite Query Manager.

2. Create a new composite query or open an existing one.

3. In the Composite Query Manager page, Fields section, optionally select an aggregate option from the Aggregate column for the field to which you want to add aggregate.

   Image: Composite Query Manager page

   This example illustrates the Composite Query Manager page. The Fields section displays the aggregate Count for the employee ID field.

   

4. Click the Actions icon of an output field and select the Edit Group By option to display the Edit Group By page.

   Note: The Edit Group By option is available in the Actions list for all output fields, including the aggregate fields. However, in the Edit Group By page, the aggregate fields are not included in the Group By Fields list.

   Image: Edit Group By page

   This example illustrates the Edit Group By page. All fields except the aggregate field from the output field list are included and grouped automatically.

   

5. Select the Include check boxes for the fields to include those fields in the Group By clause.

   Otherwise, deselect the Include check boxes for the fields to remove those fields from the Group By clause.

   Note: You may deselect the Include check box for any field to remove that field from the Group By clause, but your removal may cause an error if the removed field must be included in the Group By clause.

6. Optionally, click the Move Up or Move Down link to reorder the fields in the Group By clause.

7. Click the OK button to apply the changes and return to the Composite Query Manager page.

8. Optionally, click the SQL button to verify the Group By clause in the generated SQL.

Aggregated fields and aggregated expression fields should not be included in the Group By list. Be default, aggregated fields are not included in the Group By list. However, if an expression field contains aggregation, it is included in the Group By list by default because the system has read it as a regular non-aggregate expression field. To avoid the error when you run SQL, you must remove the aggregate expression field from the Group By list by deselecting its Include check box or by removing it from the output field list.

After the Group By clause is generated, the order of the Group By columns is persisted, so removing or adding output fields does not reset the order of the Group By columns. If you add an additional output field to the composite query for the existing Group By clause, then the new output field is added to the end of the Group By clause. You should ensure that the fields listed in the Group By clause are in the correct order. To reset all Group By fields, you must remove all aggregate values from the output fields, add those fields back to the output field list, and then regenerate the default Group By clause.