Build an Aggregation Custom Value Grouping on Two Fields

This page explains how to create an aggregation based on multiple fields, using as example counting the number of customers per Gender and Country.

To select a combination of multiple fields to create an aggregation upon, you first need to combine those fields as a new one you can later use when building the Aggregation.

Create a Step1 Selection with a Custom Value in the Target Definition that is using CONCAT() SQL function between Country and Gender field.

Use this formula:

CONCAT("DESelect_DEMO_Customers"."Country",'-',"DESelect_DEMO_Customers"."Gender")

As a result, you will have a Custom Value field added to the Target Data Extension, which has a unique value field, combining the fields that you need to use for your aggregation:

Create a Step2 Selection, in which the Selection Criteria is the Target Data Extension of Step1.

In the Target Definition of Step2 Selection, and given our example “counting number of customers per Gender and Country” we need the following fields to be added to your Target Data Extension.

Country: (From the Selected Data Extension)

Gender: (From the Selected Data Extension)

Count: Custom Value, in which we are going to create our Aggregation, to count customers for each Country AND Gender.

The result will be the Sum of CustomerId in the DESelect_Demo_Customers for each Country-Gender in Step1.

The preview will show the correct results, but they are duplicated (one row per CustomerId, even though the CustomerId column is not shown). Hence, you need to remove duplicates, for example, by making both Country and Country-Gender a primary key.

Finally, the result will be what you're looking for: