Problem with aggregate function in ACS

Avatar

Avatar

pierre-yvesm520

Avatar

pierre-yvesm520

pierre-yvesm520

06-12-2018

Hello, I have a problem using aggregate function in ACS.

When I add an additionnal data wich is based on the SUM() function in a simple query activity, I get a PostgreSQL Error : column "n1.irecipientid" must appear in the GROUP BY clause or be used in an aggregate function.

I can't do any aggregate, could you help me please.

Thanks

View Entire Topic

Avatar

Avatar

anasso83525035

Avatar

anasso83525035

anasso83525035

16-12-2018

Salut Pierre-Yves,

Pour un besoin similaire, j'ai configuré la même Query que toi. ça semble logique, mais j'ai pris le temps de comprendre pourquoi ça ne fonctionne pas, avant de trouver une solution de contournement que je partage volontiers en anglais :

The aggregate functions don't work with simple fields, only with collections :

1648115_pastedImage_0.png

1648116_pastedImage_1.png

The average profile age can be calculated by creating a Profile collection using Additional data in the main query and the Enrichment activity :

  • In your main query add as an additional data a constant text field labeled 'Collection Link' :

1648117_pastedImage_4.png

  • Add an Enrichment activity. In advanced Relations, Add an element with this definition :

1648118_pastedImage_6.png

  • Add new additional data with the profile collection we just created :

1648119_pastedImage_8.png

  • In the new window that pops up, go to Data and Create Element as follow :

1648120_pastedImage_10.png

Confirm all and run workflow. The field Avg(Age) should be added to Profile Collection.