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]

More info needed:
What table is your businesses stored in?
Where are company codes stored?
What is the relationship between locations and businesses/company codes?

[alffrey]

More info needed:
What table is your businesses stored in?
User(sys_user)

Where are company codes stored?
field is u_company_code on sys_user table

What is the relationship between locations and businesses/company codes?
Stored on same table (sys_user)

[john_roberts]

Sorry, forgot to ask you to provide an example of what the output you are hoping for.

Do you want the count of user's company codes by location?
LocationA - 50 users
CompanyCodeA - 10
CompanyCodeB- 40
LocationB - 25 users
CompanyCodeA -10
CompanyCodeB - 10
CompanyCodeC - 5

Or are you just looking for the codes found within a location of users?
LocationA - 50 users
CompanyCodeA
CompanyCodeB
LocationB - 25 users
CompanyCodeA
CompanyCodeB
CompanyCodeC

Or something else I didn't think of?

[alffrey]

Thanks John!

Yes. I'm looking for the following information as listed below plus I'm looking for the COUNT of CompanyCode within a location. So in the following example, LocationA, Count of CompanyCodes would be 2. In LocationB, COUNT of CompanyCodes would be 3. Then I want to evaluate the COUNT.

If Count of CompanyCodes > 1
do something
Else


Do you want the count of user's company codes by location?
LocationA - 50 users
CompanyCodeA - 10
CompanyCodeB- 40
LocationB - 25 users
CompanyCodeA -10
CompanyCodeB - 10
CompanyCodeC - 5