Creating a GROUP BY clause

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. The result of a GROUP BY clause is that a set of groups of rows is created. Each row in the result table represents the set of rows for which the grouping expression is equal.

A grouping sets specification lets multiple grouping clauses be specified in a single statement.

SQL support in the SQL wizard and SQL Builder is dependent on the level of support provided by your database vendor. Grouping support:

• DB2: Group By, Rollup, Cube and Grouping Sets

• Oracle: Group By, Rollup and Cube

• Sybase: Group By

• Cloudscape: Group By

See the documentation for your database product for more information on grouping.

To specify a group:

1. Switch to the Data perspective.

2. Open your SELECT statement in the SQL Builder.

3. In the Design pane, click the Groups tab.

4. On the Groups page, click Grouping root in the list. The list shows the grouping expression currently defined. Initially, this list contains only Grouping root because nothing has been added to the grouping expression yet.

5. To add to the Grouping root, double-click the first empty row under Column.

6. 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.

7. To modify a nested grouping type, select the type in the grouping expression list. After selecting the grouping type, you can complete the details for that grouping type, including nesting other groupings, by selecting the grouping type in the Grouping root list and adding the columns, expressions, and groups to it in the Columns list.

8. To modify the type of grouping, select the grouping type from the Grouping root box, then select a type from the Type list.

Adding a Group Conditions HAVING clause

The group conditions table is available on the Group Conditions page of the Design pane. The condition is displayed in a HAVING clause. The purpose of a HAVING clause is to narrow the expression that is evaluated to group the result set. To enter a value in one of the table cells, click the empty row in the table, and then click in the first cell and select a value from the list. You can build expressions in the Column and Value columns and select an operator or operand from the Operator and And/Or columns.

The following list describes the columns in the Conditions Table:

Column

This column can contain either a table column or an expression. To enter an expression, click Build Expression to open the Expression Builder wizard.

Operator

Select an operator for the condition.

Value

This column can contain a column, value, or expression. Click the empty row in the table, then click in the first cell to type a value, select a column, or select Build Expression to open the Expression Builder wizard.

And/Or

If you need multiple predicates in your HAVING clause, specify either the AND or the OR operator between each predicate.

After you have completed a cell, press Enter for the change to take effect.

To delete a row, right-click the row and then click Remove Condition from the pop-up menu.

 

Parent topic

SELECT statement retrieves data from a database and returns it in the form of a table. It can be embedded in an application or used interactively.">Creating a SELECT statement