GlideAgg That Counts Distinct Number

[kemmy1]

I have a simple task to get some data from my scoped table and I'm having a hard time at it.  I'm going to use the Incident table to make things easier to understand.  I'd like to run through all Incident Caller ID's and simply ask "How Many Categories Did they use?"  (I don't want to know WHAT category it was, but just how many).

I started out with this and it's great:

 var callerID = '';

 var ga = new GlideAggregate('incident');

 ga.addAggregate('COUNT', 'number' );

 ga.groupBy('caller_id'); 

ga.setLimit('1); //I know there is no apostrophe, but the body of this post was turning it into something funky

ga.query();

while (ga.next()) {

callerID = ga.caller_id;

gs.info(ga.getDisplayValue('caller_id') + " | " + ga.getAggregate('COUNT', 'number'));

//so far so good

//I get a caller_id that has 5 incidents (2 with a category of Security and 3 with a category of Software) that's 2 distinct Categories (and here is where I get stuck:)

//After the While I scripted this:

var ga2 = new GlideAggregate('incident');

ga2.addEncodedQuery('caller_id=' + callerID);  //should be 5 records

ga2.addAggregate('COUNT', 'category'); 

ga2.groupBy('category); // should be 2 records?

ga2.query();

if (ga2.next()) {

    gs.info(ga2.getAggregate('COUNT', 'category'));  //gives me the count of all categories in on the table

}

}

[Itallo Brandão · Accepted solution]

Hi kemmy1,

You are making it harder than it needs to be! You actually don't need the second GlideAggregate loop (which is great news for performance).

ServiceNow's GlideAggregate supports a powerful feature specifically for this: COUNT(DISTINCT ...).

Instead of querying the user and then querying their categories, you can ask the database to "Group by User and count the distinct Categories" in a single shot.

Here is the optimized script:

var ga = new GlideAggregate('incident');

// 1. Group by the User
ga.groupBy('caller_id');

// 2. Count how many UNIQUE values exist in the 'category' field for this group
ga.addAggregate('COUNT(DISTINCT', 'category'); 

ga.query();

while (ga.next()) {
    var callerName = ga.getDisplayValue('caller_id');
    
    // 3. Retrieve the distinct count
    var uniqueCategories = ga.getAggregate('COUNT(DISTINCT', 'category');
    
    gs.info(callerName + " has used " + uniqueCategories + " distinct categories.");
}

Why your original script failed: In your second loop (ga2), ga2.getAggregate('COUNT', 'category') returns the number of incidents inside that category bucket (e.g., "5 Network Incidents"), not the number of buckets themselves. By using COUNT(DISTINCT, the database handles the logic for you efficiently.

If this solution works for you, please mark it as Accepted Solution.

Best regards,
Brandão.

[kemmy1]

Thank you so much!  This works!