Aggregate Functions Clarification

REST
1

I have just realized by comparing a codeless and REST Console example that are producing the same result that I have been misunderstanding aggregate functions.

I thought that an aggregate function would calculate on the appropriate column (I am using Max on field = distance), and then for all the other columns the API brings back, I was expecting the corresponding values from the same record in the database (as the maximum distance).
But that is not what happens…I can now see the values for the other columns are pulled from the first record in the database that has the same OwnerId (which is my group by field…).

I cannot see how I could set up what I want in the REST console. Can I get a hint about doing it in codeless?

2

Hello @Paul_McCullen

Do you want to load a list of objects where the distance property is max for each ownerId, is that correct?
Could you please provide your appId in order to see your Data Schema.

3

Hi Vladimir

Yes, that is what I want.

The id is DE9727E5-8683-E098-FF56-9D23955C8300.

Thanks
Paul

4

Now I see your issue

Let me explain why it happens, let’s say you’ve got the following records list in your data table

{ owner:'user-id-1', distance:10, title:'10m' },
{ owner:'user-id-1', distance:100, title:'100m' },
{ owner:'user-id-1', distance:1000, title:'1000m' },

{ owner:'user-id-2', distance:20, title:'20m' },
{ owner:'user-id-2', distance:200, title:'200m' },

{ owner:'user-id-3', distance:3000, title:'3000m' },

and when you aggregate (max(distance)) the list and group by owner the database merges these items in an unpredictable way and as result you receive:

{ owner:'user-id-1', distance:1000, title:'10m' },

{ owner:'user-id-2', distance:200, title:'20m' },

{ owner:'user-id-3', distance:3000, title:'3000m' },

So, I assume to get it in a single API request is not possible.
What about to make 2 API calls:

  1. request data as you already do
  2. request data using the following whereClause = (owner='user-1' and distance=1000) or (owner='user-2' and distance=200) or (owner='user-3' and distance=3000),

What do you think?

5

Hi Vladimir

Yes, agree. That is is exactly what is happening…

Thanks for confirming it cannot be done with one API call…I suspected that and just wanted to confirm. I will play around with the second API. I think I can also do it a second way too; because I know the workout calculations, for example if distance = 2000, then time will always be 22 mins (for whoever is doing the workout - the times and calcs are uniform), I can actually use the distance to reference another table that holds this calculations. I will look into it but that way, owner is not needed and I just use the distance to pick up time, etc.

I was experimenting with some kind of loop in codeless that took each distance and then found the corresponding time…

Thanks again
Paul

Thanks again
Paul

1 Like