20. Get the Accounts that in total have more than 50 euro in Orders

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: You want to get the Company Name of all Accounts that in total have more than 50 euro in Orders.

Note: this scenario uses feature Formula's in Subqueries, which is available in DESelect Plus and Advanced.

1. Selection Criteria

On the selections overview screen, click on New to create a new selection. 

Drag data extension DESELECT_DEMO_Accounts to the Selected Data Extensions section.

Drag field Id to the Filters section:

• Choose criteria: In Results
• From Available Data Extensions, select data extension DESELECT_DEMO_Orders and drag-drop it on the drop-section in the newly created filter. 
• Choose field AccountId

Now we have indicated we want to get the Accounts whose Id appears in the Orders data extension. However, we want to filter on only the Accounts with more than 50 euro worth of Orders.

• Click on the funnel icon on the newly created filter.
• In the modal that opens up:
  • Drag field Amount to the Filters section.
  • Choose criteria: Greater than or equal to.
  • Click on the formula icon, and choose Sum. You'll see the formula icon turn green and the field name change to Sum of Amount.
  • For the value, choose: 50.

We will now be filtering based on the sum of the amounts of the Orders; this has to be 50 or more. 

2. Target Data Extension

Hit the Create Data Extension button, enter a name, eg. Accounts with larger Orders, and press Save.

Select the following fields by either double-clicking on them or dragging them to the section on the right:

• Company Name from DESELECT_DEMO_Accounts

Click Save Data Extension.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with field Company Name from DESELECT_DEMO_Accounts.

The results table shows only Accounts that in total have more than 50 euro in Orders.