Support Topics Documentation Slack YouTube Blog

DATA MINER: #6 Retrieve Data With Aggregate Functions

  • This mission includes an demo video. Here are the specs:

image

  • At the end of the video, the final results look like this:

image

  • And here are my results:

image

This is my REST statement URL: My REST Statement
…48199200/14FAC9EE-9B3C-4F55-9EDD-F57F8C3B0847/data/Country?
property=Continent&property=Count(Continent) as CountryCount,
MIN(LifeExpectancy) as MinLifeExpectancy,
MAX(LifeExpectancy)as MaxLifeExpectancy
&groupBy=Continent
&where=IndepYear>1900

Looks good to me! Any idea why my this Mission isn’t catching and being marked complete?

Hi Paul,

Could you please share what the the request URL looks like in your case?

Regards,
Mark

Here’s an unedited copy and paste version.

https://api.backendless.com/CAA21524-8666-2BDF-FF58-C3E148199200/14FAC9EE-9B3C-4F55-9EDD-F57F8C3B0847/data/Country?property=Continent&property=Count(Continent)as CountryCount,MIN(LifeExpectancy) as MinLifeExpectancy,MAX(LifeExpectancy) as MaxLifeExpectancy&groupBy=Continent&where=IndepYear>1900

And this is a screen print:

@stanislaw.grin could you please check the query and see where the differences are with we track?

Thanks,
Mark

That URL was constructed both manually and with the NEW Aggregate Functions option in the REST console. Most of it was solved there, but I couldn’t get the MIN & MAX properties to work correctly, so I added them manually. While we’re on the subject, I’m just curious, what would be the correct way to add the MIN & MAX properties?

In these example the MIN function is entered exactly the same way as it was in the REST statement, but still generates an error.

Hello @Townsend

you have incorrect value in Having clause. You can not use as statement there, only logical expressions are available like in where clause. You should construct some thing like the following:

Let me know if I was not clear, or you have any other question

I’m also interested in understanding what I’m doing wrong here.

This is the full API request:
https://api.backendless.com/721FC2A7-E947-DBF2-FF27-F810EF2B1C00/9F3C8ADE-6F0B-4D0F-8445-3CB5D8920442/data/Country?property=Count(objectId)%20as%20CountryCount,Continent,MIN(LifeExpectancy)%20as%20MinLifeExpectancy&groupBy=Continent

Detailed description of the params and compared against the requirements of the task.

  1. Rest API :white_check_mark:
  2. table Country :white_check_mark: -> data/Country
  3. Grouped by Continent column :white_check_mark: -> &groupBy=Continent
  4. following aggregate function:
    count of countries in each continent named as CountryCount :white_check_mark: -> COUNT(objectId) %20as%20CountryCount
    Minimum life expectancy with property name MinLifeExpectancy :white_check_mark: -> MIN(LifeExpectancy)%20as%20MinLifeExpectancy
  5. Continent property included in request and response :white_check_mark:

What is wrong here? I’ve noticed that in the Video there is also MaxLifeExpectancy and IndepYear - but its not required based on the written requirements. Also, there are changes as in video we use props but in REST console we use now property.

Will appreciate help here.
Grzegorz

Got it working,

  1. COUNT needs to be capitalized
  2. I used props:

https://api.backendless.com/721FC2A7-E947-DBF2-FF27-F810EF2B1C00/9F3C8ADE-6F0B-4D0F-8445-3CB5D8920442/data/Country?props=COUNT(objectId)%20as%20CountryCount,Continent,MIN(LifeExpectancy)%20as%20MinLifeExpectancy&groupBy=Continent

1 Like

All of supported functions are not case-sensitive.
Could you try with different names for count function: COUNT, Count, couNt. All of them should be workable.

We had a bug in the tracking of users actions. So in some cases tracking service required the name of the functions was in upper case. But Backendless API do not have such requirement.
The problem was fixed and the new release will be in a next couple of days.