How to aggregate Count in loop

How can i achieve this and get my results in table format as shown in the screenshot?
‘Pioneer’ is a moderator that (A)activated 25 adverts and (R)rejected 3 adverts making a (T)total of 28 adverts moderated by pioneer for today
‘moderator’ is another moderator that (A)activated 30 adverts and (R)rejected 3 adverts making a (T)total of 33 adverts moderated by moderator for today
The master table has a ‘moderatedOn’ date field and a ‘moderatedBy’ String field and ‘status’ String field
How do i get a count for each moderator to achieve the above screenshot
My first approach goes something like this
For all moderators with usernames in String arraylist
whereClause = moderatedby = username[i] AND moderatedOn today AND status = ‘active’
Get a count of this
Then do same for status = ‘rejected’
Am at a loss as how to even get the count and then aggregate all the results to form a table as shown in the screenshot.

Hello @Nkekere_Tommy_Minimann,
could you please provide your APP ID with that table scheme you’ve described here?
It’s hard to understand what is the moderated by means for the Moderator table.



I have a table called ‘master’. This table has a string field called ‘moderatedBy’, and a date field called ‘moderatedOn’. It also has another string field called ‘status’. How do I create that table in the screenshot by aggregating the counts from these fields over a period of time eg today, this week, this month…etc
Basically I want to know how many adverts each moderator(moderatedBy) set as ‘active’, (status)and how many as ‘rejected’(status) for the specified period of time(moderatedOn)

First of all I can advise you to set the moderatedBy type to Data Relation not String:

For this schema I suggest using groupBy to retrieve amount of different statuses for each moderator.
My example is in Swift, I assume it would be very similary in Java or other languages:

let queryBuilder = DataQueryBuilder()
queryBuilder.relationsDepth = 1 // to receive moderator object for master
// in case you want to filter records in time period please use similar where clause
// e.g. this one selects records from Nov 1, 2021 to Nov 18, 2021
queryBuilder.whereClause = "moderatedOn > 1635724800000 AND moderatedOn < 1637193600000"
// adverts amount is calculated using the COUNT aggregate function = ["status", "Count(objectId) as adverts"]
// this grouping is made to show active and rejected adverts separately for each moderator
queryBuilder.groupBy = ["moderatedBy", "status"]"Master").find(queryBuilder: queryBuilder, responseHandler: { masters in
    var moderatorsInfo = [[String : Any]]()

    for master in masters {
        guard let status = master["status"] as? String,
        let adverts = master["adverts"] as? NSNumber,
        let moderatedBy = master["moderatedBy"] as? [String : Any],
        let name = moderatedBy["name"] as? String,
        let objectId = moderatedBy["objectId"] as? String
        else { return }
        // after we got adverts amount (active or rejected) for moderator, we can put that record
        // to the moderatorsInfo array to use in future
        if let index = moderatorsInfo.firstIndex(where: { $0["objectId"] as? String == objectId }) {
            var moderator = moderatorsInfo[index]
            moderator[status] = adverts
            moderatorsInfo[index] = moderator
        else {
            moderatorsInfo.append(["objectId": objectId, "name": name, status: adverts])
    // finally we got an array of moderators for selected dates
    for moderator in moderatorsInfo {
        print("[\(moderator["name"] ?? "NO NAME")] - active: \(moderator["active"] as? NSNumber ?? 0), rejected: \(moderator["rejected"] as? NSNumber ?? 0)")
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")

More info about aggregate functions can be found here: Overview - Backendless SDK for iOS API Documentation

Hope this would help you.


This looks really good, but how do I get this in java

The first few lines look almost like their Java counterpart but later lines are difficult to translate. Who can help?

Hello @Nkekere_Tommy_Minimann

With the data structure you have now, it might look like:

  private static void test()
    DataQueryBuilder builder = DataQueryBuilder.create();
    builder.setWhereClause( "moderatedOn > 1635724800000 AND moderatedOn < 1637193600000" );
    builder.setProperties( "ModeratedBy", "status", "Count(objectId) as adverts" );
    builder.setGroupBy( "moderatedBy", "status" );

    Backendless.Data.of( "Master" ).find( builder, new AsyncCallback<List<Map>>()
      public void handleResponse( List<Map> listObjects )
        List<Moderator> moderatorsInfo = new ArrayList<>();

        listObjects.forEach( map -> {
          final String moderatorName = (String) map.get( "ModeratedBy" );
          if( moderatorName == null )

          Moderator moderator = findModeratorByName( moderatorsInfo, moderatorName );
          if( moderator != null )
            setStatus( moderator, map );
            Moderator newModerator = new Moderator( moderatorName );
            setStatus( newModerator, map );
            moderatorsInfo.add( newModerator );
        } );

        System.out.println( listObjects );
        System.out.println( moderatorsInfo );

      public void handleFault( BackendlessFault backendlessFault )

    } );

  private static void setStatus( Moderator moderator, Map map )
    final Object status = map.get( "status" );
    if( status.equals( "Active" ) )
      moderator.setActive( ( Integer ) map.get( "adverts" ) );
    else if( status.equals( "Rejected" ) )
      moderator.setRejected( ( Integer ) map.get( "adverts" ) );

  private static Moderator findModeratorByName( List<Moderator> moderatorsInfo, String moderatedBy )
        .filter( moderator -> moderator.getName().equals( moderatedBy ) )
        .orElse( null );


import lombok.Data;

public class Moderator
  private String name;
  private int active;
  private int rejected;

  public Moderator( String name )
  { = name;

Maybe you should consider changing the database structure, something like: Master Table 1:N Table Post.
Database normalization