GlideAggregate GroupBy giving Incorrect Count

[Sumit Pandey1]

Hi All,

When executing the below script, It shows me the count which is different from the count shown in list view with exact same query string! (Going to list view, and applying group by on Manager field).

I have already checked there are no Query Business Rules active on that table.

var myManagerList = [];
var getResourceManagers = new GlideAggregate('sys_user');
getResourceManagers.addEncodedQuery('active=true^company=c7af548d37323100d4e8daa543990e36^managerISNOTEMPTY');
getResourceManagers.addAggregate('COUNT','manager');
getResourceManagers.groupBy('manager');
getResourceManagers.query();  
while (getResourceManagers.next()) 
{
var manager = getResourceManagers.getAggregate('COUNT', 'manager');
myManagerList.push(manager);
}

gs.info('myManager length == '+myManagerList.length); //Output: 3757, List View Count: 3703

[Anish Reghu]

I executed the above code with and without (.length)

WITH (.length)

var myManagerList = [];
var getResourceManagers = new GlideAggregate('sys_user');
getResourceManagers.addEncodedQuery('active=true^company=0c441abbc6112275000025157c651c89^managerISNOTEMPTY');
getResourceManagers.addAggregate('COUNT','manager');
getResourceManagers.groupBy('manager');
getResourceManagers.query();  
while (getResourceManagers.next()) 
{
var manager = getResourceManagers.getAggregate('COUNT', 'manager');
myManagerList.push(manager);
}

gs.print('myManager length = '+myManagerList.length);


**********************************************************
//OUTPUT - 1

WITHOUT (.length)

var myManagerList = [];
var getResourceManagers = new GlideAggregate('sys_user');
getResourceManagers.addEncodedQuery('active=true^company=0c441abbc6112275000025157c651c89^managerISNOTEMPTY');
getResourceManagers.addAggregate('COUNT','manager');
getResourceManagers.groupBy('manager');
getResourceManagers.query();  
while (getResourceManagers.next()) 
{
var manager = getResourceManagers.getAggregate('COUNT', 'manager');
myManagerList.push(manager);
}

gs.print('myManager length = '+myManagerList);


************************************************************

//OUTPUT - 19 

And the list view count:

So, try and let me know if this helps.

Anish Reghu

Mark the response helpful or Mark as Correct answer if this helps you reach a solution.

[Sumit Pandey1]

Hi Anish, Your first script prints the array length, while the second script is printing the count of records where 'anish reghu' is manager.

I need the array length to print correctly which is not happening for large datasets.

[Willem]

If the query is the same then count/result should be the same.

I tested this and logged number of records as well.

Script below returned:

When I go to the list and group by:

Records will return the number of records returned.

var myManagerList = [];
var records = 0;
var getResourceManagers = new GlideAggregate('sys_user');
getResourceManagers.addEncodedQuery('active=true^company=c7af548d37323100d4e8daa543990e36^managerISNOTEMPTY');
getResourceManagers.addAggregate('COUNT', 'manager');
getResourceManagers.groupBy('manager');
getResourceManagers.query();
while (getResourceManagers.next()) {
    var manager = getResourceManagers.getAggregate('COUNT', 'manager');
    myManagerList.push(manager);
    records += parseInt(manager);
}

gs.info('myManager length == ' + myManagerList.length); 
gs.info('Records == ' + records); 

make sure in both cases you use exactly the same query.

Also not sure about there not being any active before query business rule on sys_user. Per default there is a Before query business rule filtering out inactive records for non-admins.

[Sumit Pandey1]

Hey Willem,

The query is exactly same and we do not have any active query business rules and I am executing this from my admin account.

When I execute the same script on small dataset(1k record), the record count and myManagerList count prints the same, but when I try the same script on large dataset, the count starts to vary. It is super wierd.

[Willem]

So your array length is more then the number of managers?