Get Aggregate Data

Owned by Al Lusher (Unlicensed)
Last updated: Aug 16, 2023 by Jessica Tomkins
3 min read

 

This action retrieves aggregate data (e.g. sum, min, max, avg, count) for groups of records.

From which database?

If Profound.js has been configured for multiple connections, this allows you to select the appropriate database connection for this step. If a database is not selected, the default connection will be used.

From which database tables?

Select one or more tables.

                       If more than one table is selected, you will have to join the tables together.

                     

                       After clicking the Join selected tables button you will be able to indicated how the tables are joined by dragging and dropping the key fields

                       

Select/enter aggregate function(s) and alias name(s) for column(s)

For each table column for which you would like to get aggregate data:

  • For the first input column:

  •  

    • Select from the dropdown list one or more aggregate functions, and/or

    • Enter your own aggregate function names.

  • For the second input column:

  •  

    • Specify the alias name(s) for the column(s) in the result.

    • The alias names in the second input column are automatically populated from the first input column as follows:

    •  

      • If there is only one function specified, then the alias name is the column name.

      • If more than one functions are specified, then each of the alias name is in the format of functionName_columnName.

      • For example: if you specify function "avg" for column "amount", then the alias name is "amount". If you specify functions "avg" and "sum" for column "amount", then the alias names are "avg_amount" and "sum_amount".

    • You can override the auto-populated alias names with your own list of comma-separated alias names.

Selection criteria expression

Provide a WHERE-clause expression to identify the records. Use the dropdown to create the expression without having to write code manually.

Question marks (?) in the expression are used to identify dynamic values, which are also referred to as selection parameter values. For each question mark, you will be prompted to provide a selection parameter value, where you can select or manually type dynamic content.

Specify group by

Check this box to group the records by specific groups.

Select column(s) to group by

Select one or more columns to group by.

  • If GROUP BY is used:

  •  

    • The GROUP BY columns will be in the result, in addition to the columns for which aggregate functions are specified.

    • There may be zero or more records returned. Zero record is returned if the WHERE criteria are not met.

    • The data returned is an array of zero or more elements. Each element is a record.

  • If GROUP BY is not used:

  •  

    • There is always one record returned, even if the WHERE criteria are not met.

    • The data returned is an object.

 

Specify order by

Check this box to sort the records in a specific order.

Select column(s) to order by

Select one or more columns to sort by. The default sort order is ascending. To change the order to descending, manually type the DESC keyword after the column name.

Where do you want to place the record(s)?

The record(s) can be placed into a grid, screen, work variable, or property.

Enter work variable name

A work variable is accessible by other steps within the same routine. Some work variables, like 'activeGridRecord' are implied and don't have to be explicitely defined by you.

Enter API output property name

The API output object represents the data sent in response to the API request.