How to group by in service now?

Question

I am new in service now and I don't know yet how to manipulate the data unlike in MySQL and SQL. I just want to know how can I group by the data.

Here is my code to show the data:

var group = new GlideRecord("table");
    
group.addEncodedQuery('u_active=True');
group.orderBy('u_order_in_services');
group.query();
            
while (group.next()) {
gs.info(group.group_name);  

}

The result is:

Group 1
Group 1
Group 2 
Group 2
Group 3
Group 3

Needed result is:

Group 1
Group 2
Group 3

Answer 1

With either GlideQuery or GlideAggregate, to get the result you want you should use the groupBy method. It's hard to tell precisely what you're doing since you didn't give us the actual table name, but, following your example, the code should look like this:

new GlideQuery('table')
  .where('u_active', true)
  .groupBy('group_name')
  .aggregate('count')
  .select()
  .map(function (record) { return record.group.group_name })
  .forEach(GQ.debug);

A comparable GlideAggregate example would look like this:

var ga = GlideAggregate('table');
ga.addQuery('u_active', true);
ga.groupBy('group_name');
ga.addAggregate('COUNT')
ga.query();

while (ga.next()) {
  gs.debug(ga.getValue('group_name'));
}

Note, I removed the orderBy clause of both these queries since it makes less sense when we're grouping the results. Also, if this really is a custom table, I would triple-check that the group_name field isn't actually named u_group_name. If so, you'll need to update my examples to work properly.

Answer 2

Use GlideAggregate (instead of of GlideRecord) if you need a GROUP BY.

There is an introduction here:

• https://developer.servicenow.com/blog.do?p=/post/glideaggregate/

Answer 3

You could use GlideQuery for this, example:

var group = new global.GlideQuery('sys_user_group')
    .where('active', true)
    .orderBy('name')
    .select('name')
    .toArray(100); 

GQ.jsonDebug(group)

GlideQuery API Documentation