0 ×

Database GroupBy

KNIME Base Nodes version 3.7.0.v201812031623 by KNIME AG, Zurich, Switzerland

This node allows rows to be grouped by the selected columns from the input database table. Within the dialog, an SQL GROUP BY clause is interactively created by selecting the columns to group by and the columns to aggregate.

The columns to aggregate can be either defined by selecting the columns directly, by name based on a search pattern or based on the data type. Input columns are handled in this order and only considered once e.g. columns that are added directly on the "Manual Aggregation" tab are ignored even if their name matches a search pattern on the "Pattern Based Aggregation" tab or their type matches a defined type on the "Type Based Aggregation" tab. The same holds for columns that are added based on a search pattern. They are ignored even if they match a criterion that has been defined in the "Type Based Aggregation" tab.

The "Manual Aggregation" tab allows you to change the aggregation method of more than one column. In order to do so select the columns to change, open the context menu with a right mouse click and select the aggregation method to use.

In the "Pattern Based Aggregation" tab you can assign aggregation methods to columns based on a search pattern. The pattern can be either a string with wildcards or a regular expression. Columns where the name matches the pattern but where the data type is not compatible with the selected aggregation method are ignored. Only columns that have not been selected as group column or that have not been selected as aggregation column on the "Manual Aggregation" tab are considered.

The "Type Based Aggregation" tab allows to select an aggregation method for all columns of a certain data type e.g. to compute the mean for all numerical columns (DoubleCell). Only columns that have not been handled by the other tabs e.g. group, column based and pattern based are considered. The data type list to choose from contains basic types e.g String, Double, etc. and all data types the current input table contains.

A detailed description of the available aggregation methods can be found on the 'Description' tab in the node dialog.

Options

Groups

Group settings
Select one or more column(s) according to which the group(s) is/are created.

Advanced settings

Column naming
The name of the resulting aggregation column(s) depends on the selected naming schema.
  • Keep original name(s): Keeps the original column names.
  • Aggregation method (column name): Uses the aggregation method first and appends the column name in brackets
  • Column name (aggregation method): Uses the column name first and appends the aggregation method in brackets
Add COUNT(*)
Tick this option to add a column that contains the result for the COUNT(*) operation.
column name
The name of the COUNT(*) column. Only enabled if the "Add COUNT(*)" option is selected.

Manual Aggregation

Aggregation settings
Select one or more column(s) for aggregation from the available columns list. Change the aggregation method in the Aggregation column of the table. You can add the same column multiple times. In order to change the aggregation method of more than one column select all columns to change, open the context menu with a right mouse click and select the aggregation method to use.
Parameter
The parameter column shows an "Edit" button for all aggregation operators that require additional information. Clicking on the "Edit" button opens the parameter dialog which allows changing the operator specific settings.

Pattern Based Aggregation

Aggregation settings
Use the "Add" button to add a new row with a search pattern to the aggregation settings. The search pattern can either be a string with wildcards or a regular expression. Supported wildcards are * (matches any number of characters) and ? (matches one character) e.g. KNI* would match all strings that start with KNI such as KNIME whereas KNI? would match only strings that start with KNI followed by a fourth character. Double click the "Search pattern" cell to edit the pattern. The cell is colored in red if the pattern is invalid.
RegEx
Tick this option if the search pattern is a regular expression otherwise it is treated as string with wildcards ('*' and '?').
Parameter
The parameter column shows an "Edit" button for all aggregation operators that require additional information. Clicking on the "Edit" button opens the parameter dialog which allows changing the operator specific settings.

Type Based Aggregation

Aggregation Settings
Select one or more data type from the available type list. Change the aggregation method in the Aggregation column of the table. You can add the same data type multiple times. The list contains standard types e.g. Double, String etc. and all types of the input table.
Parameter
The parameter column shows an "Edit" button for all aggregation operators that require additional information. Clicking on the "Edit" button opens the parameter dialog which allows changing the operator specific settings.

Input Ports

Table in database to apply group by

Output Ports

Table in the database with grouped rows

Best Friends (Incoming)

Best Friends (Outgoing)

Workflows

Update Site

To use this node in KNIME, install KNIME Base Nodes from the following update site:

Wait a sec! You want to explore and install nodes even faster? We highly recommend our NodePit for KNIME extension for your KNIME Analytics Platform.