Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Problem with aggregate function in ACS

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 1
Level 4
asish_kumarp599
Level 4

Likes

37 likes

Total Posts

62 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Shape 1
Boost 5
Boost 3
Boost 25
View profile

Avatar
Validate 1
Level 4
asish_kumarp599
Level 4

Likes

37 likes

Total Posts

62 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Shape 1
Boost 5
Boost 3
Boost 25
View profile
asish_kumarp599
Level 4

16-12-2018

Thanks for sharing the screenshots! Here are my comments:

  • I think you are using the avg function available in the 'Expression Editor'?  Unfortunately, these Aggregate functions available in query editor are not quite efficient in the 'Additional data' section of query editor . Also, to fulfill your requirement there is no Group By function with Aggregate function here.
  • However, these Aggregate functions works quite well in 'Extract file' activity. You can use the Avg function in the output column section and the use Transfer file activity to export the data. I am not sure this works for you but I see this is only way to get the avg function work.

Answers (10)

Answers (10)

Avatar

Avatar
Validate 1
Level 2
anasso83525035
Level 2

Likes

6 likes

Total Posts

30 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Give Back 3
Give Back
Boost 5
Boost 3
View profile

Avatar
Validate 1
Level 2
anasso83525035
Level 2

Likes

6 likes

Total Posts

30 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Give Back 3
Give Back
Boost 5
Boost 3
View profile
anasso83525035
Level 2

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.

Avatar

Avatar
Boost 1
Level 1
jyotiy2857946
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Boost 1
Affirm 1
View profile

Avatar
Boost 1
Level 1
jyotiy2857946
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Boost 1
Affirm 1
View profile
jyotiy2857946
Level 1

21-05-2020

Hi,

 

You can perform aggregate in Query, as shown below:

jyotiy2857946_0-1590046671099.png

 

Add Group by line and you will be able to execute query without error.

 

Thanks.

 

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

17-12-2018

OK Thanks to both of you for your answers.

asish kumarp59945245  : I'll find a way to perform the calculation I need without using the agregate functions on the advanced expression editor then.

anasso21228396​ : I could effectively use the collections to perform agregate calculations (but i'll have to adapt my workflow before)

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

16-12-2018

1647874_pastedImage_0.png

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

16-12-2018

1647873_pastedImage_0.png

Avatar

Avatar
Validate 1
Level 4
asish_kumarp599
Level 4

Likes

37 likes

Total Posts

62 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Shape 1
Boost 5
Boost 3
Boost 25
View profile

Avatar
Validate 1
Level 4
asish_kumarp599
Level 4

Likes

37 likes

Total Posts

62 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Shape 1
Boost 5
Boost 3
Boost 25
View profile
asish_kumarp599
Level 4

15-12-2018

Can you please share the Screenshot of your Properties & General tab. It may occur if your targeting dimension is not Profile.

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

12-12-2018

Hello,

The query in on profiles for example, see below.

- Profile : Has children = Yes

And in additionnal Data :

- Average(age)

1645052_pastedImage_0.png

1645053_pastedImage_1.png

This cause an error in the query  :

1645054_pastedImage_2.png

I hope my post is detailed enough.

Thanks in advance

Avatar

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asktam1410
Level 3

11-12-2018

Post the query which you are trying

Avatar

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
pierre-yvesm520
Level 1

Likes

3 likes

Total Posts

7 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
pierre-yvesm520
Level 1

11-12-2018

Hello,

I dont have such options, I working on Adobe Campaign Standard (ACS) and the only option avaliable is to remove duplicate rows :

(The screenshot is from a french version)1644181_pastedImage_0.png

Avatar

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asktam1410
Level 3

11-12-2018

did you select group by enabled in the output cololums as well as  enable the following options under advanced parameters.

Group_BY.JPG