How to design conditions for GROUP BY clauses

The GROUP BY clause in a SELECT statement is used to group rows with the same values by one or more columns. The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. It can also be used with aggregate functions to group summarized data. The SELECT statement returns a single row per group.

To work with GROUP BY clauses, open the Tabbed Editor by clicking Show Tabbed Editor icon Show Tabbed Editor on the Query toolbar or Query menu.

The Group By tab of the Tabbed Editor displays the GROUP BY clause divided into three panes: Columns, Grouped by, and Expression.

To create a GROUP BY clause

1. In the Columns pane, select the column(s) to group data by and click Add the column icon Add Column. This will move columns to the Grouped By pane.

2. For multiple columns: In the Grouped By pane, use Move up icon Move Up and Move down icon Move Down to change the order of columns.

To exclude the column from the list, select it and then click Remove the column icon Remove Column.

Group by the column

3. In the Expression field, enter the expression such as a column or a non-aggregate calculation on a column. Then, click Add expression icon Add Expression. This moves the expression to the Grouped By pane.

Add the expression to the GROUP BY clause

4. Switch to the Text view to preview the SQL statement.

Preview the GROUP BY clause filtered by the columns and expression

Note

All columns in the SELECT list of the SELECT query (on the Selection tab of Tabbed Editor) must appear in the GROUP BY clause unless the column is used only in an aggregate function.