ACC TIPS | How to aggregate data in workflow

Every now and then, you may receive a request to aggregate data within an Adobe Campaign Classic workflow. However, if you group by something other than the targeting dimension, you may encounter an error such as “‘w0.iid’ must appear in the GROUP BY clause or be used as an aggregate function.

The reason behind Adobe Campaign always adding the primary key to the SELECT clause, in aggregate queries, remains unknown. However, we must find workarounds to deal with this situation. If you cannot avoid grouping by the targeting dimension, here is a fix for you:

Please note that this option is only available in the query activity and cannot be found in the enrichment feature.

1. In query Add data – create as many aggregates as you want use group by check box where needed.
2. Save add data modal window when you are done.
3. Open Edit additional data – on the bottom section you will see Advanced parameters section
4. In Advanced parameters check following option – Disable automatic adding of the primary keys of targeting dimension.
5. Finish