How to group data by one column and sort the groups by another column in ServiceNow?

Mahesh Bachhav
Kilo Expert

‎04-28-2022 09:08 PM

Is there a way to group data by one column and sort the groups by another column in ServiceNow using GlideAggregate()?
 
I tried the below code but it groups the result by both columns 'caller_id' and 'opened_at' instead of just 'caller_id' column.
 
var count = new GlideAggregate('incident');
count.addAggregate('COUNT', 'caller_id');
count.setGroup(true);
count.groupBy('caller_id')
count.orderBy('opened_at')
count.query();
while (count.next()) {
var caller_id= count.caller_id;
gs.info(caller_id);
gs.info(count.getEncodedQuery());
}
 
Output is - 
*** Script: 415d0648db379010360724f405961952
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at
*** Script: 62826bf03710200044e0bfc8bcbe5df1
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at
*** Script: a2826bf03710200044e0bfc8bcbe5ded
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at
 
 
Labels:
0 Helpfuls
  • 1,238 Views
1 REPLY 1

Chetan Mahajan
Kilo Sage
Kilo Sage

‎04-28-2022 09:21 PM

Hi Mahesh,

                         Try below code

var countGroupArr = [];
var caller_id;
var count = new GlideAggregate('incident');
count.addAggregate('COUNT', 'caller_id');
count.setGroup(true);
count.groupBy('caller_id');
count.orderBy('opened_at');
count.query();
while (count.next()) {
caller_id = count.caller_id;
countGroupArr.push(caller_id);
}

countGroupArr.sort(); // sorted array
gs.info(countGroupArr);

 

Kindly mark correct and helpful if applicable