Skip to main content

Varicent ELT Help Center

Aggregate

Abstract

Summarize data by aggregating.

Use the Aggregate tool to summarize your data by aggregating using operators. You can use the Aggregate tool to perform one of the following scenarios:

  • Perform an aggregate operation on a column.

  • Arrange data into groups.

  • Arrange your data into groups and apply an aggregate operation.

For example, you can use the Aggregate tool with the operator COUNT to count the number of rows in your data.

Figure 1. Aggregate tool example
Aggregate tool example


The aggregate tool offers the following operators: MAX, MIN, COUNT, SUM, AVERAGE, MEDIAN, OR, AND, and STDEV. For more information, see the Aggregate tool configuration section.

Input

The Aggregate tool requires one data input.

Configuration

Use the following configuration options to configure the Remove Outliers tool.

Configuring the Aggregate tool

  1. Go to the Pipes module from the side navigation bar.

  2. From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.

  3. In the Pipe builder, add at least one data source to your pipe. For more information on adding a data source, see the Data tool.

  4. Click symon_add_icon.png + Tool.

    The Tools modal opens where you can add tools, such as the Aggregate tool to your pipe.

  5. In the Tools modal, search for Aggregate and then click + Add tool.

    Tip

    You can also find the Aggregate tool in the Calculate section.

  6. Click the tool node and drag the line to the next tool to connect the tools. If you need to undo the action, click the line and then click Unlink.

  7. In the configuration pane, enter the following information:

    Table 32. Aggregate tool configuration

    Field

    Description

    Aggregate columns Group by

    Select the Group by checkbox if you want to group the columns that you want to aggregate based on the values of an existing column.

    Decimal places

    Select the checkbox to add decimal places. Enter the number of decimal places, or use the arrows to increase or decrease the value. The limit is 14 decimal places.

    Column

    Select the column to aggregate from your data set.

    Operation

    Select the operation to use from the drop-down menu:

    • SUM: Use to find the sum of the value.

    • COUNT: Use to count the value.

      Note

      You can use the COUNT operation without selecting an aggregate column.

    • AVERAGE: Use to find the average value.

    • MIN: Use to find the minimum value.

    • MAX: Use to find the maximum value.

    • MEDIAN: Use to find the median value.

    • OR: Use to find specified conditions. One of the records must be true.

      Note

      The OR operator must be used only with boolean values.

    • AND: Use to find specified conditions. All of the records must be true.

      Note

      The AND operator must be used only with boolean values.

    • STDEV: Use to find the standard deviation across the values.

    Column name

    Enter a name for the new column. For example, if you are using the Aggregate tool to calculate your sellers estimated amount of commission, you may name the column Total commission.

    + Add column

    Click to add a new aggregate column.



Usage example

Let's use the Aggregate tool to return the total row count for our data set. In this example, we will be trying to discover how many calls were placed to customer service by our customers. The following is an image of the data set we are working with:

Figure 2. Sample customers data set
Sample customers data set


Using the Aggregate tool for total of customer service calls

  1. In the Aggregate configuration pane, let's configure the following values:

    1. Leave the Group by and Decimal places checkboxes blank.

    2. In the Column field, use the Customer Service Calls column to aggregate.

    3. In the Operation field, select the COUNT operator.

    4. In the Column name field, enter the name of the new column, Amount of calls.

  2. Build your pipe.

  3. In the new column, you will now see that the amount of calls placed to customer service is 3333.

    AggregateDataExample2025.png