GlideAggregate - Scoped

GlideAggregate enables you to easily create database aggregation queries.

The scoped GlideAggregate class is an extension of GlideRecord and provides database aggregation (COUNT, SUM, MIN, MAX, AVG) queries. This functionality can be helpful when creating customized reports or in calculations for calculated fields. The GlideAggregate class works only on number fields.

When you use GlideAggregate on currency or price fields, you are working with the reference currency value. Be sure to convert the aggregate values to the user's session currency for display. Because the conversion rate between the currency or price value (displayed value) and its reference currency value (aggregation value) might change, the result may not be what the user expects.

Note: When using an on-premise system, the database server time zone must be set to GMT/UTC for this class to work properly.

Scoped GlideAggregate - addAggregate(String agg, String name)

Adds an aggregate.

Table 1. Parameters
Name Type Description
agg String Name of the aggregate to add, for example, COUNT, MIN, or MAX
name String Name of the column to aggregate. Null is the default.
Table 2. Returns
Type Description
void
var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
}
//Number of incidents varies depending on the current state
//of the incident table
gs.info('Number of incidents: ' + incidents);

Scoped GlideAggregate - addDomainQuery(GlideRecord)

Adds a query of a domain to the aggregate.

Table 3. Parameters
Name Type Description
d GlideRecord object GlideRecord object that specifies the domain.
Table 4. Returns
Type Description
GlideQueryCondition The query result, which includes the records in the specified domain.

var d = new GlideRecord("domain");
d.get('name', 'ACME'); 

var ga = new GlideAggregate("incident"); //looking in the Incident [incident] table.
ga.initialize();
ga.addDomainQuery(d); //queries for the ACME domain in the Incident table.
ga.addAggregate("COUNT"); //Counts the records.
ga.query();
ga.next();
gs.print(" Row count in effective domain: "+ ga.getAggregate("COUNT")); 
//Prints the number of records in the Incident table that belong to the ACME domain.

Scoped GlideAggregate - addEncodedQuery(String query)

Adds an encoded query to the other queries that may have been set for this aggregate.

Table 5. Parameters
Name Type Description
query String An encoded query to add to the aggregate.
Table 6. Returns
Type Description
void
//Number of incidents varies depending on the current state
//of the incident table
var count = new GlideAggregate('incident');
count.addEncodedQuery('active=true');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next())
   incidents = count.getAggregate('COUNT');
gs.info(incidents);

Scoped GlideAggregate - addQuery(String name, String operator, String value)

Adds a query to the aggregate.

Table 7. Parameters
Name Type Description
name String The query to add.
operator String The operator for the query.
value String The list of values to include in the query.
Table 8. Returns
Type Description
GlideQueryCondition The query condition.
//Number of incidents varies depending on the current state
//of the incident table
var count = new GlideAggregate('incident');
count.addQuery('active', '=','true');
count.addAggregate('COUNT', 'category');
count.query();  
while (count.next()) {
   var category = count.category;
   var categoryCount = count.getAggregate('COUNT', 'category');
   gs.info("There are currently " + categoryCount + " incidents with a category of " + category);
}
Output:
There are currently 1 incidents with a category of database
There are currently 5 incidents with a category of hardware
There are currently 42 incidents with a category of inquiry
There are currently 4 incidents with a category of network
There are currently 4 incidents with a category of request
There are currently 7 incidents with a category of software

Scoped GlideAggregate - addNotNullQuery(String fieldName)

Adds a not null query to the aggregate.

Table 9. Parameters
Name Type Description
fieldname String The name of the field.
Table 10. Returns
Type Description
GlideQueryCondition The scoped query condition.
var count = new GlideAggregate('incident');
  count.addNotNullQuery('short_description');
  count.query();   // Issue the query to the database to get all records
  while (count.next()) {   
     // add code here to process the aggregate
  }

Scoped GlideAggregate - addNullQuery(String fieldName)

Adds a null query to the aggregate.

Table 11. Parameters
Name Type Description
fieldName String The name of the field.
Table 12. Returns
Type Description
GlideQueryCondition The scoped query condition.
var count = new GlideAggregate('incident');
  count.addNullQuery('short_description');
  count.query();   // Issue the query to the database to get all records
  while (count.next()) {   
     // add code here to process the aggregate
  }

Scoped GlideAggregate - getAggregate(String agg, String name)

Gets the value of an aggregate from the current record.

Table 13. Parameters
Name Type Description
agg String The type of the aggregate, for example, SUM or Count.
name String Name of the field to get the aggregate from.
Table 14. Returns
Type Description
String The value of the aggregate.
var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
}
//Number of incidents varies depending on the current state
//of the incident table
gs.info('Number of incidents: ' + incidents);

Output: Number of incidents: 63

Scoped GlideAggregate - getAggregateEncodedQuery()

Gets the query necessary to return the current aggregate.

Table 15. Parameters
Name Type Description
none
Table 16. Returns
Type Description
String The encoded query to get the aggregate.
var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.groupBy('category');
count.query();  
while (count.next()) {
    gs.info(count.getAggregateEncodedQuery());
}
Output:
category=database
category=hardware
category=inquiry
category=network
category=request
category=software

Scoped GlideAggregate - getEncodedQuery()

Retrieves the encoded query.

Table 17. Parameters
Name Type Description
none
Table 18. Returns
Type Description
String The encoded query.
var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();
gs.info(count.getEncodedQuery());
Output:
ORDERBYcategory^GROUPBYcategory

Scoped GlideAggregate - getRowCount()

Retrieves the number of rows in the GlideAggregate object.

Table 19. Parameters
Name Type Description
none
Table 20. Returns
Type Description
Number The number of rows in the GlideAggregate object.
var count = new GlideAggregate('incident');
  count.addAggregate('MIN', 'sys_mod_count');
  count.addAggregate('MAX', 'sys_mod_count');
  count.addAggregate('AVG', 'sys_mod_count');
  count.groupBy('category');
  count.query();
  gs.info(count.getRowCount());
  while (count.next()) {  
     var min = count.getAggregate('MIN', 'sys_mod_count');
     var max = count.getAggregate('MAX', 'sys_mod_count');
     var avg = count.getAggregate('AVG', 'sys_mod_count');
     var category = count.category.getDisplayValue();
     gs.info(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
  }
Output:
6
Database Update counts: MIN = 8 MAX = 48 AVG = 28.0000
Hardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250
Inquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714
Network Update counts: MIN = 3 MAX = 37 AVG = 18.6000
Request Update counts: MIN = 5 MAX = 39 AVG = 13.4000
Software Update counts: MIN = 4 MAX = 98 AVG = 24.0000

Scoped GlideAggregate - getTableName()

Retrieves the table name associated with this GlideAggregate object.

Table 21. Parameters
Name Type Description
none
Table 22. Returns
Type Description
String The table name.
var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();
gs.info(count.getTableName());

Scoped GlideAggregate - getValue(String name)

Gets the value of a field.

Table 23. Parameters
Name Type Description
name String The name of the field.
Table 24. Returns
Type Description
String The value of the field.
var count = new GlideAggregate('incident');
  count.addAggregate('MIN', 'sys_mod_count');
  count.addAggregate('MAX', 'sys_mod_count');
  count.addAggregate('AVG', 'sys_mod_count');
  count.groupBy('category');
  count.query();   
  while (count.next()) {  
     var min = count.getAggregate('MIN', 'sys_mod_count');
     var max = count.getAggregate('MAX', 'sys_mod_count');
     var avg = count.getAggregate('AVG', 'sys_mod_count');
     var category = count.category.getDisplayValue();
  }
gs.info("Current category is: " + count.getValue('category'));

Output: Current category is: software

Scoped GlideAggregate - GlideAggregate(String tableName)

Creates a GlideAggregate object on the specified table.

Table 25. Parameters
Name Type Description
tableName String Name of the table.
var count = new GlideAggregate('incident');

Scoped GlideAggregate - groupBy(String name)

Provides the name of a field to use in grouping the aggregates.

May be called numerous times to set multiple group fields.

Table 26. Parameters
Name Type Description
name String Name of the field.
Table 27. Returns
Type Description
void
var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();   
while (count.next()) {  
     var min = count.getAggregate('MIN', 'sys_mod_count');
     var max = count.getAggregate('MAX', 'sys_mod_count');
     var avg = count.getAggregate('AVG', 'sys_mod_count');
     var category = count.category.getDisplayValue();
     gs.info(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
}
Output:
Database Update counts: MIN = 8 MAX = 48 AVG = 28.0000
Hardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250
Inquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714
Network Update counts: MIN = 3 MAX = 37 AVG = 18.6000
Request Update counts: MIN = 5 MAX = 39 AVG = 13.4000
Software Update counts: MIN = 4 MAX = 98 AVG = 24.0000

Scoped GlideAggregate - hasNext()

Determines if there are any more records in the GlideAggregate object.

Table 28. Parameters
Name Type Description
none
Table 29. Returns
Type Description
Boolean True if there are more results in the query set.
var agg = new GlideAggregate('incident');
agg.addAggregate('AVG', 'sys_mod_count');
agg.groupBy('category');
agg.query();
while (agg.hasNext()) {
    agg.next();
    var avg = agg.getAggregate('AVG', 'sys_mod_count');
    var category = agg.category.getDisplayValue();
    gs.info(category + ': AVG = ' + avg);
}
Output:
Database: AVG = 32.5000
Hardware: AVG = 12.0000
Inquiry / Help: AVG = 7.6667
Network: AVG = 24.0000
Request: AVG = 16.4000
Software: AVG = 27.0833

Scoped GlideAggregate - next()

Moves to the next record in the GlideAggregate.

Table 30. Parameters
Name Type Description
none
Table 31. Returns
Type Description
Boolean True if there are more records in the query set; otherwise, false.
var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
   gs.info(incidents);
}

Scoped GlideAggregate - orderBy(String name)

Orders the aggregates using the value of the specified field. The field will also be added to the group-by list.

Table 32. Parameters
Name Type Description
name String Name of the field to order the aggregates by.
Table 33. Returns
Type Description
void
var agg = new GlideAggregate('incident');
agg.addAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count', 'category');
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count', 'category');
  agg2.orderBy('category');
  gs.info(category + ": Current number of incidents:" + count);
}
Output:
database: Current number of incidents:2
hardware: Current number of incidents:8
inquiry: Current number of incidents:28
network: Current number of incidents:5
request: Current number of incidents:5
software: Current number of incidents:11

Scoped GlideAggregate - orderByAggregate(String agg, String fieldName)

Orders the aggregates based on the specified aggregate and field.

Table 34. Parameters
Name Type Description
agg String Type of aggregation.
fieldName String Name of the field to aggregate.
Table 35. Returns
Type Description
void

ga.addAggregate(‘COUNT’, ‘category’);
 
ga.orderByAggregate('count', 'category');
 
ga.query();
 
while(ga.next()) {
  gs.info(‘Category ’ + ga.category + ‘ ‘ + ga.getAggregate(‘COUNT’, ‘category’));
  }

Output:

Category inquiry 18
Category software 11
Category hardware 7
Category network 5
Category request 5
Category  4
Category database 2

Scoped GlideAggregate - orderByDesc(String name)

Sorts the aggregates in descending order based on the specified field. The field will also be added to the group-by list.

Table 36. Parameters
Name Type Description
name String Name of the field.
Table 37. Returns
Type Description
void
var agg = new GlideAggregate('incident');
agg.addAggregate('count', 'category'); 
agg.orderByDesc('category'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count', 'category');
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count', 'category');
  agg2.orderBy('category');
  gs.info(category + ": Current number of incidents:" + count);
}
Output:
software: Current number of incidents:11
request: Current number of incidents:5
network: Current number of incidents:5
inquiry: Current number of incidents:28
hardware: Current number of incidents:8
database: Current number of incidents:2

Scoped GlideAggregate - query()

Issues the query and gets the results.

Table 38. Parameters
Name Type Description
None
Table 39. Returns
Type Description
void
var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
}
gs.info('Number of incidents: ' + incidents);

Scoped GlideAggregate - setGroup(Boolean b)

Sets whether the results are to be grouped.

Table 40. Parameters
Name Type Description
b Boolean When true the results are grouped.
Table 41. Returns
Type Description
void

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT', 'category');
 
ga.setGroup(true);
ga.groupBy("category");
 
ga.query();
 
while(ga.next()) {
  gs.info('Category ' + ga.category + ' ' + ga.getAggregate('COUNT', 'category'));
  }

Output:

Category database 2
Category hardware 7
Category inquiry 18
Category network 5
Category request 5
Category software 11