Retrieving Unique Objects

Unique objects can be retrieved from a column of the requested table by setting the true value in the setDistinct method, The distinct parameter is a boolean value which defaults to false.

The distinct property can be also used with Aggregate Functions such as AVERAGE, COUNT, and SUM. When applied to an aggregate function, the distinct property ensures that only distinct values are considered in the calculation, eliminating any duplicates from the result.

For example, when using the distinct property with the COUNT function, it counts only the unique occurrences of a particular column, disregarding any repetitions. Similarly, when combined with AVERAGE or SUM, the distinct property ensures that only distinct values are considered in the calculation, providing accurate results based on unique values within the dataset.

Consider the following data table called Employees:

This table contains the column firstName, and by passing the true value to the setDistinct method, only unique values will be returned in the response:

DataQueryBuilder queryBuilder = DataQueryBuilder.create();  
queryBuilder.addProperties("firstName");  
queryBuilder.setDistinct(true);  

// sync  
List<Map> persons = Backendless.Data.of("Employees").find( queryBuilder );  

// async  
Backendless.Data.of("Employees").find(queryBuilder, new AsyncCallback<List<Map>>()  
{  
  @Override  
  public void handleResponse( List<Map> employees )  
  {  
    // data handling logic  
  }  

  @Override  
  public void handleFault( BackendlessFault backendlessFault )  
  {  
    // error handling logic  
  }  
} );

The query returns the following result which contains only unique names:

[  
    {  
        "___class": "Employees",  
        "firstName": "Alex"  
    },  
    {  
        "___class": "Employees",  
        "firstName": "Alice"  
    },  
    {  
        "___class": "Employees",  
        "firstName": "Bob"  
    }  
]

Aggregate Functions

The distinct parameter can be also used with such aggregate functions as:

• AVERAGE
• COUNT
• SUM

All examples presented further use sample data from the Employees data table presented in the beginning of this topic.

Average

Suppose you need to calculate an average value for a set of objects, but you want to skip identical records. The following example will retrieve only unique values from the age column and calculate the average age of all employees.

DataQueryBuilder queryBuilder = DataQueryBuilder.create();  
queryBuilder.addProperties( "Avg(distinct age) as averageAge" );  

// sync  
Integer averageAge = (Integer) Backendless.Data.of("Employees").find(queryBuilder).get(0).get("averageAge");  

// async  
Backendless.Data.of("Employees").find(queryBuilder, new AsyncCallback<List<Map>>()  
{  
  @Override  
  public void handleResponse(List<Map> result )  
  {  
    Integer averageAge = (Integer) result.get(0).get("averageAge");  
    // data handling logic  
  }  
  @Override  
  public void handleFault(BackendlessFault backendlessFault)  
  {  
    // error handling logic  
  }  
} );

Count

The example below will count only unique values in the firstName column and return an integer value in the response:

DataQueryBuilder queryBuilder = DataQueryBuilder.create();  
queryBuilder addProperties("Count(distinct firstName) as uniqueNamesNumber")  

//sync  
Integer uniqueNamesNumber = (Integer) Backendless.Data.of("Employees").find(queryBuilder).get(0).get("uniqueNamesNumber");  

//async  
Backendless.Data.of("Person").find(queryBuilder, new AsyncCallback<List<Map>>(  
{  
 @Override  
 public void handleResponse(List<Map> result)  
 {  
   Integer uniqueNamesNumber = (Integer) result.get(0).get("uniqueNamesNumber");  
   // data handling logic  
 }  

 @Override  
 public void handleFault(BackendlessFault backendlessFault)  
 {  
   // error handling logic  
 }  
} );

Sum

The example below will calculate the sum of unique values in the bonusPayment column and return an integer value in the response:

DataQueryBuilder queryBuilder = DataQueryBuilder.create();  
queryBuilder addProperties ("Sum(distinct bonusPayment) as sumUniquePayments")  

//sync  
Integer sumUniquePayments = (Integer) Backendless.Data.of("Employees").find(queryBuilder).get(0).get("sumUniquePayments");  

//async  
Backendless.Data.of("Employees").find(queryBuilder, new AsyncCallback<List<Map>>(  
{  
 @Override  
 public void handleResponse(List<Map> result)  
 {  
   Integer sumUniquePayments = (Integer) result.get(0).get("sumUniquePayments");  
   // data handling logic  
 }  
 @Override  
 public void handleFault(BackendlessFault backendlessFault)  
{  
   // error handling logic  
 }  
} );

Codeless Reference

Consider the following data table called Employees:

AVERAGE

Suppose you need to calculate the average age of your employees, while excluding duplicates. As shown in the data table schema above, there are two identical records with the name "Bob Peterson". that have the value of 40 in the "age" column.

The example below returns the average age only for unique records stored in the Employees data table:

The operation returns the following result in the response:

COUNT

Suppose you need to count the number of employees, while excluding duplicates. Considering the data table schema provided above, there are two identical entities with the name "Bob Peterson".

The example below counts the total number of unique employee names in the Employees data table:

The operation returns the following result in the response:

SUM

Suppose you need to calculate the sum of all bonus payments, while excluding duplicates. Considering the data table schema provided above, there are two identical records that have the 360 value stored in the "bonusPayment" column:

The example below excludes the duplicate entity and calculates the sum of unique values:

The operation returns the following result in the response: