Is it possible to perform multiple Aggregate Count Functions

[alffrey]

The following is a script that queries the user table and performs and aggregate count function on the location field. The script returns a list of locations with a count of users at that location. This part works.

For example
Orlando 120
Cincinnati 500
Charlotte 120
San Diego 1000
Seattle 80

Within these locations are separate businesses that have a company code. I would like to count all locations and then all companies within a location and then perform an action on the company record. I tried to perform another aggregate function in the WHILE loop on the location field that I was processing but no dice. Would anyone know how to accomplish this?

Thanks!


---------------------------------------------------------------------------------------------------------------------------------------------------
// This script reads the SN User table, counts all the locations
//
//Create an aggregate query on the users tables
var count = new GlideAggregate('sys_user');

//set condition for active users in all locations
count.addQuery('active','true'); //Active Users

//set aggregate field to location to get user count by location
count.addAggregate('COUNT','location');
//execute the query
count.query();

//loop through the results
while (count.next()) {
var locID = count.getValue("location"); //sysid value
var locCount = count.getAggregate('COUNT', 'location'); //count of number of locations

// get real record so we don't keep querying the same record
var loc = new GlideRecord("cmn_location");
//get record from cmn_location table and match locID, locID is sysid
if (!loc.get(locID)) {
continue; // can't find location record
}
var locname = loc.getDisplayValue();
gs.log("***ALF Location " + locname + " Location Count" + " " + locCount );

}

[john_roberts]

I don't think we can nest aggregates but this should do the trick.

// This script reads the SN User table, counts all the locations
//
//Create an aggregate query on the users tables
var count = new GlideAggregate('sys_user');

// set condition for active users in all locations
count.addQuery('active', 'true'); // Active Users

// set aggregate field to location to get user count by location
count.addAggregate('COUNT', 'location');
// execute the query
count.query();

// loop through the results
while (count.next()) {
var locID = count.getValue("location"); // sysid value
var locCount = count.getAggregate('COUNT', 'location');
var locname = count.getDisplayValue('location');
gs.log("***ALF Location " + locname + " Location Count" + " " + locCount);

//query users in this location
var locUsers = new GlideAggregate("sys_user");
locUsers.addQuery('location', locID);
locUsers.addAggregate('COUNT', 'u_company_code');
locUsers.query();
while (locUsers.next()) {
var compCode = locUsers.getDisplayValue('u_company_code');
var compCount = locUsers.getAggregate('COUNT', 'u_company_code');
gs.log("      Company: " + compCode + " = " + compCount);
}

}

[alffrey]

Thanks John! That provided me mostly what I needed. I added a variable and incremented by 1 within the while loop to count the number of companies by location. I appreciate your assistance on finding a solution to provide this information.