can we take two fields as groupBy at one time in glide aggregate scenario

[akkk]

Hello team , 

can we take two fields as groupBy at one time in glide aggregate scenario

[Manmohan K]

Hi @akkk 

Yes, you can group by multiple fields at once

Below is a snippet of code supplied by ServiceNow using multiple groupBy fields

var tableName = 'cmdb_rel_ci'; // TARGET TABLE
var encodedQuery = 'parentISNOTEMPTY^typeISNOTEMPTY^childISNOTEMPTY'; // APPLY ANY FILTER CONDITIONS HERE
var identifierFields = 'parent,type,child'; //USED TO IDENTIFY THE DUPLICATES
var batch = 5000 ; //SETS THE LIMIT FOR HOW MANY RECORDS ARE IN THE GLIDERECORD

var identifiers = identifierFields.split(','); //split up the groupby fields, we'll use this one later

var agg = new GlideAggregate(tableName);
agg.addQuery(encodedQuery);
agg.setLimit(batch);

// ADD THE COMBINATION OF UNIQUE IDENTIFIERS HERE
for(var i = 0; i < identifiers.length; i++) {
   agg.groupBy(identifiers[i]);
}
// DON'T BOTHER READING ANY RECORDS THAT AREN'T DUPLICATE
agg.addAggregate('COUNT');
agg.addHaving('COUNT','>','1'); // only where 1 or more duplicates exist
agg.query();

[Karthiga S]

Hi @akkk 

Yes. That is possible. Sample script below

var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT');
agg.groupBy('priority');
agg.groupBy('category');
agg.query();

while(agg.next()) {
gs.print('Priority: ' + agg.priority + ' Category: ' + agg.category + ' Count: ' + agg.getAggregate('COUNT'));
}

Please mark it Correct and Hit Like if you find this helpful!

Regards,

Karthiga

[Harish Bainsla]

GlideAggregate in ServiceNow does not natively support grouping by multiple fields directly in a single query. Each GlideAggregate query can group by only one field at a time.

If you need to group by multiple fields simultaneously, you might need to consider alternative approaches. Here are a couple of options:

1. Nested GlideAggregate Queries: You can achieve a similar effect of grouping by two fields by using nested GlideAggregate queries. First, group by one field, and then within each group, run another GlideAggregate query to further group by the second field.

2. Process Results After Retrieval: Retrieve the data using a single field for grouping, and then process the results in your script to group by the second field programmatically.

3. example

   var aggregate = new GlideAggregate('your_table_name');
   aggregate.addGroupBy('first_field');
   aggregate.addGroupBy('second_field');
   aggregate.addAggregate('COUNT', 'your_field_to_count');

   aggregate.query();

   while (aggregate.next()) {
   var groupValue1 = aggregate.getValue('first_field');
   var groupValue2 = aggregate.getValue('second_field');
   var count = aggregate.getAggregate('COUNT', 'your_field_to_count');
   // Process the results here
   }