Querying using grouping management
In this example, we want to run a query to find all email domains targeted over 30 times during previous deliveries.
- Which table needs to be selected?The recipient table (nms:recipient)
- Fields to be selected in output columns?Email domain and primary key (with count)
- Data grouping?Based on email domain with a count of primary keys above 30. This operation is carried out with the Group by + Having option. Group by + Having lets you group data ("group by") and make a selection of what was grouped ("having").
To create this example, apply the following steps:
- Open the Generic query editor and choose the Recipient table ( nms:recipient ).
- In the Data to extract window, select the Email domain and Primary key fields. Run a count on the Primary key field.For more on primary key counts, refer to this section .
- Check the Handle groupings (GROUP BY + HAVING) box.
- In the Sorting window, sort email domains in descending order. To do this, check Yes in the Descending sort column. Click Next .
- In Data filtering , select Filtering conditions . Go to the Target elements window and click Next .
- In the Data grouping window, select the Email domain by clicking Add .This data grouping window is only displayed if the Handle groupings (GROUP BY + HAVING ) box was checked.
- In the Grouping condition window, indicate a primary key count greater than 30 since we only want email domains targeted more than 30 times to be returned as results.This window appears when the Manage groupings (GROUP BY + HAVING) box was checked: this is where the grouping result is filtered (HAVING).
- In the Data formatting window, click Next : no formatting is necessary here.
- In the data preview window, click Launch data preview : here, three different email domains targeted over 30 times are returned.