Grouping grid

In the grouping grid, you can specify the columns to group by or aggregate on.

The grid is populated with the columns that you selected on the Selection page. You can change the selected columns or select new ones, which will be reflected in the selection your query makes.

The grid has the following fields:

  • Column expression. Identifies the column to be included in the query. You can modify the selections from the Selection page, or build a column expression.
    • Job parameter. A dialog box appears offering you a choice of available job parameters. This allows you to specify the value to be used in the query at run time (the stage you are using the SQL builder from must allow job parameters for this to appear).
    • Expression Editor. An expression editor dialog box appears, allowing you to specify an expression that represents the value to be used in the query.
    • Data flow variable. A dialog box appears offering you a choice of available data flow variables (the stage you are using the SQL builder from must support data flow variables for this to appear).
    • Lookup Column. You can directly select a column from one of the tables in the table selection canvas.
  • Column Alias. This allows you to specify an alias for the column. If you select an aggregation operation for a column, SQL builder will automatically insert an alias of the form Alison; you can edit this if required.
  • Output. This is selected to indicate that the column will be output by the query. This is automatically selected when you add a column to the grid.
  • Distinct. Select this check box if you want to add the DISTINCT qualifier to an aggregation. For example, a COUNT aggregation with the distinct qualifier will count the number of rows with distinct values in a field (as opposed to just the not-null values). For more information about the DISTINCT qualifier, see SQL Properties Dialog Box.
  • Aggregation. Allows you to select an aggregation function to apply to the column (note that this is mutually exclusive with the Group By option). See Aggregation Functions for details about the available functions.
  • Group By. Select the check box to specify that query results should be grouped by the results in this column.