Using SQL aggregate functions

A transport map can be configured to use an SQL aggregate function. SQL aggregate functions return a single value, calculated from values in a column. The following SQL aggregate functions are supported per database column:

  • AVERAGE
  • COUNT
  • MINIMUM
  • MAXIMUM
  • STANDARD DEVIATION
  • SUMMARY
  • VARIANCE

Before you begin, consider the following:

  • Only one SQL aggregate function can be applied to each table column. If more than one aggregate function is required for a single column an additional transport map will need to be created per SQL aggregate function for that one column. However, a transport map can be created with different columns specifying SQL aggregate functions.
  • When using an SQL aggregate function, you can specify a Where clause to limit the range of column values where the SQL aggregate function is to be applied.
  • SQL aggregate functions are not available with other database actions such as Select operations and no other database actions can be performed when selecting the Aggregate action.
  • Transport maps using SQL aggregate functions are not placed in the store and forward queue if the transport enters the Store and Forward state.
  • It is assumed that the underlying database product has implemented each SQL aggregate function.

Follow these steps to add an SQL aggregate function per database column:

  1. From the Enterprise section of the transport map, select the schema and table to use.
  2. From the Action drop-down list, select Aggregate.
    The Aggregate tab becomes available.

    Rows under Column and DB Type become populated with data from the selected database table.

  3. From the Aggregate tab, select the first row under the Function column.

    The following SQL aggregate function are available:

    Function Description
    AVG Adds the values of all the rows in a table column and divides that value by the number of rows that match the criteria specified in a Where clause. If a Where clause is not used, the values of all the rows for that column are added together and then divided by the total number of rows in the table.
    The Local database does not support the AVG aggregate function.
    COUNT Counts the number of rows in a table column that match the criteria specified in a Where clause. If a Where clause is not used, then all the rows in the column are counted.
    MIN Selects the minimum value from the rows a table column that match the criteria specified in a Where clause. If a Where clause is not used, then the minimum value is selected from all the rows in the column.
    MAX Selects the maximum value from the rows in a table column that match the criteria specified in a Where clause. If a Where clause is not used, then the maximum value is selected from all the rows in the column.
    STDDEV Calculates the standard deviation for all the rows in a table column that match the criteria specified in a Where clause. If a Where clause is not used, then the standard deviation is calculated for all the values in the column.
    The Local database does not support the STDDEV aggregate function.
    SUM Adds the value of all the rows in a table column that match the criteria specified in a Where clause. If a Where clause is not used, then the values of all the rows for that column are added together.
    VARIANCE Calculates the variance for all the rows in a table column that match the criteria specified in a Where clause.If a Where clause is not used, then the variance is calculated for all the values in that column.
    The Local database does not support the VARIANCE aggregate function.
  4. Select the aggregate function that should be applied to the column. To specify aggregate functions for other columns, navigate to the column and repeat the aggregate function selection step. After you have mapped aggregate functions to columns your screen may look as shown below. Specify an upper limit on the number of rows to be processed by entering a numeric value in the Max. Rows field. The default value is 1.


    You can select the Map Table button to automatically create output map variables.
    These generated map variables will appear in the Output tab and on the Variable column on the Aggregate tab as shown below.

    The Output tab will consist of a collection of map variables whose values will be populated with the results of the execution of the select.

    You can change the logical variable names generated using the Map Table button. If you do so, you must manually edit the variable name and remap it to the column in the Aggregate tab.
    If you don't want to include columns in your Select Aggregate statement you can select the variable in the Output tab, right-click and choose Clear. This removes the output variable from the Output tab and also clears the variable name from the column.

  5. You can limit the number of rows being considered for aggregate operations by specifying a Where clause. When you select the Where button a popup window allows you to select columns for the Where clause. This is similar to when you specify the Where clause for a Select Transport map. See Selecting columns (Select).
  6. You can group the aggregate data by columns that were not mapped to aggregate functions. To specify these columns select the Group By button, and it will bring up a popup window as shown below.
    Select the column and then select the Add button to add the column to the Group By by list. The selected columns appear in the Grouping table. Select OK when you are done.

  7. You can order the selected data by any of the columns. To specify these columns select the Order By button, and it will bring up a popup window as shown below.

    Select the column and then select the Add button to add it to the Order By list. You can further select the sort order of the column by selecting Ascending or Descending as shown in the figure. Select OK when you are done.


  8. Select the Save button to save your transport map definition to the node.