GlideAggregate - Global

GlideAggregate enables you to easily create database aggregation queries.

The 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.

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

Scoped equivalent

To use the addAggregate() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - addAggregate(String agg, String name).

function doMyBusinessRule(assigned_to, number) {
  var agg = new GlideAggregate('incident');
  agg.addQuery('assigned_to', assigned_to);
  agg.addQuery('category', number);
  agg.addAggregate("COUNT");
  agg.query();
  var answer = 'false';
  if (agg.next()) {
    answer = agg.getAggregate("COUNT");
    if (answer > 0)
      answer = 'true';
    else
      answer = 'false';
  }
  return answer; 
}

GlideAggregate - addEncodedQuery(String query)

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

Table 3. Parameters
Name Type Description
query String An encoded query string to add to the aggregate.
Table 4. Returns
Type Description
void

Scoped equivalent

To use the addEncodedQuery() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - addEncodedQuery(String query).

var agg = new GlideAggregate('incident');
agg.addAggregate('count','category'); 
agg.orderByAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); 
agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count','category');
  var query = agg.getQuery();  
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count','category');
  agg2.orderByAggregate('count', 'category');
  agg2.orderBy('category');
  agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
  agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
  agg2.addEncodedQuery(query);
  agg2.query();
  var last = "";
  while (agg2.next()) {
     last = agg2.getAggregate('count','category');      
  }
  gs.log(category + ": Last month:" + count + " Previous Month:" + last);
 
}

GlideAggregate - addHaving(String name, String operator, String value)

Adds a "having" element to the aggregate e.g. select category, count(*) from incident group by category HAVING count(*) > 5.

Table 5. Parameters
Name Type Description
name String The aggregate to filter on for example, COUNT.
operator String The operator symbol for example <, >, =, !=.
value String The value to query on, for example '5'.
Table 6. Returns
Type Description
void


      
    

GlideAggregate - addTrend(String fieldName, String timeInterval)

Adds a trend for a field.

Table 7. Parameters
Name Type Description
fieldName String The name of the field for which trending should occur.
timeInterval String The time interval for the trend. The following choices are available: Year, Quarter, Date, Week, DayOfWeek, Hour, Value.
Table 8. Returns
Type Description
void


      
    

GlideAggregate - getAggregate(String agg, String name)

Gets the value of an aggregate from the current record.

Table 9. 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 10. Returns
Type Description
String The value of the aggregate.

Scoped equivalent

To use the getAggregate() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - getAggregate(String agg, String name).

function doMyBusinessRule(assigned_to, number) {
  var agg = new GlideAggregate('incident');
  agg.addQuery('assigned_to', assigned_to);
  agg.addQuery('category', number);
  agg.addAggregate("COUNT");
  agg.query();
  var answer = 'false';
  if (agg.next()) {
    answer = agg.getAggregate("COUNT");
    if (answer > 0)
      answer = 'true';
    else
      answer = 'false';
  }
  return answer; 
}

GlideAggregate - getQuery()

Gets the query necessary to return the current aggregate.

Table 11. Parameters
Name Type Description
None
Table 12. Returns
Type Description
String The query.

var agg = new GlideAggregate('incident');
agg.addAggregate('count','category'); 
agg.orderByAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); 
agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count','category');
  var query = agg.getQuery();  
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count','category');
  agg2.orderByAggregate('count', 'category');
  agg2.orderBy('category');
  agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
  agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
  agg2.addEncodedQuery(query);
  agg2.query();
  var last = "";
  while (agg2.next()) {
     last = agg2.getAggregate('count','category');      
  }
  gs.log(category + ": Last month:" + count + " Previous Month:" + last);
 
}

GlideAggregate - getTotal(String agg, String name)

Returns the number of records by summing an aggregate.

Table 13. Parameters
Name Type Description
agg String The aggregate
name String The name of the field to aggregate
Table 14. Returns
Type Description
Number The total


      
    

GlideAggregate - getValue(String name)

Gets the value of a field.

Table 15. Parameters
Name Type Description
name String The name of the field.
Table 16. Returns
Type Description
String The value of the field.

Scoped equivalent

To use the getValue() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - getValue(String name).


      
    

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 17. Parameters
Name Type Description
name String Name of the field.
Table 18. Returns
Type Description
void

Scoped equivalent

To use the groupBy() method in a scoped application, use the corresponding scoped method: .Scoped GlideAggregate - groupBy(String 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();   
  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.log(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
  }

GlideAggregate - orderBy(String name)

Provides the name of a field that should be used to order the aggregates. The field will also be added to the group-by list.

Table 19. Parameters
Name Type Description
name String Name of the field used to order the aggregates.
Table 20. Returns
Type Description
void

Scoped equivalent

To use the orderBy() method in a scoped application, use the corresponding scoped method: .Scoped GlideAggregate - orderBy(String name).

var agg = new GlideAggregate('incident');
agg.addAggregate('count','category'); 
agg.orderByAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); 
agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count','category');
  var query = agg.getQuery();  
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count','category');
  agg2.orderByAggregate('count', 'category');
  agg2.orderBy('category');
  agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
  agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
  agg2.addEncodedQuery(query);
  agg2.query();
  var last = "";
  while (agg2.next()) {
     last = agg2.getAggregate('count','category');      
  }
  gs.log(category + ": Last month:" + count + " Previous Month:" + last);
 
}

GlideAggregate - orderByAggregate(String agg, String name)

Orders the aggregates based on the specified aggregate and field.

Table 21. Parameters
Name Type Description
agg String Type of aggregation, for example SUM, COUNT, MIN, MAX.
fieldName String Name of the field to aggregate.
Table 22. Returns
Type Description
void

Scoped equivalent

To use the orderByAggregate() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - orderByAggregate(String agg, String fieldName).

var agg = new GlideAggregate('incident');
agg.addAggregate('count','category'); 
agg.orderByAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); 
agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count','category');
  var query = agg.getQuery();  
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count','category');
  agg2.orderByAggregate('count', 'category');
  agg2.orderBy('category');
  agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
  agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
  agg2.addEncodedQuery(query);
  agg2.query();
  var last = "";
  while (agg2.next()) {
     last = agg2.getAggregate('count','category');      
  }
  gs.log(category + ": Last month:" + count + " Previous Month:" + last);
 
}

GlideAggregate - query()

Issues the query and gets the results.

Table 23. Parameters
Name Type Description
None
Table 24. Returns
Type Description
void

Scoped equivalent

To use the query() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - query().

var agg = new GlideAggregate('incident');
agg.addAggregate('count','category'); 
agg.orderByAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); 
agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count','category');
  var query = agg.getQuery();  
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count','category');
  agg2.orderByAggregate('count', 'category');
  agg2.orderBy('category');
  agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
  agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
  agg2.addEncodedQuery(query);
  agg2.query();
  var last = "";
  while (agg2.next()) {
     last = agg2.getAggregate('count','category');      
  }
  gs.log(category + ": Last month:" + count + " Previous Month:" + last);

GlideAggregate - setGroup(Boolean b)

Sets whether the results are to be grouped.

Table 25. Parameters
Name Type Description
b Boolean Set to true if grouping is true, otherwise set to false.
Table 26. Returns
Type Description
void

Scoped equivalent

To use the setGroup() method in a scoped application, use the corresponding scoped method: Scoped GlideAggregate - setGroup(Boolean b).

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT', 'category');
 
ga.setGroup(true);