GlideAggregate - Global

Washington DC API Reference

Release

washingtondc

ft:locale

en-US

ft:publication_title

Washington DC API Reference

ft:clusterId

crapiref

bundleId

crapiref

workflow

Creator

GlideAggregate - Global

Release version: Washingtondc

Updated February 1, 2024

15 minutes to read

GlideAggregate enables creating database aggregation queries.

The GlideAggregate class is an extension of GlideRecord and provides database aggregation (AVG, COUNT, GROUP_CONCAT, GROUP_CONCAT_DISTINCT, MAX, MIN, STDDEV, SUM) queries. This functionality can be helpful when creating customized reports or in calculations for calculated 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 to a database query.

Table 1. Parameters
Name	Type	Description
agg	String	Name of an aggregate to use. Valid values: AVG: Average value of the expression. COUNT: Count of the number of non-null values. GROUP_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma (','), and returns the result as a String. GROUP_CONCAT_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma (','), and returns the result as a String. MAX: Largest, or maximum, value. MIN: Minimum value. STDDEV: Population standard deviation. SUM: Sum of all values.
name	String	Optional. Name of the field to group the results of the aggregation by. Default: Null

Table 2. Returns
Type	Description
None

The following example shows how to use GlideAggregate functions on the Incident [incident] table.

var incidentGA = new GlideAggregate('incident');
incidentGA.groupBy('category');
incidentGA.orderByAggregate('COUNT', 'sys_mod_count');

incidentGA.addAggregate('AVG', 'sys_mod_count');
incidentGA.addAggregate('COUNT', 'sys_mod_count');
incidentGA.addAggregate('GROUP_CONCAT', 'sys_mod_count');
incidentGA.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count');
incidentGA.addAggregate('MAX', 'sys_mod_count');
incidentGA.addAggregate('MIN', 'sys_mod_count');
incidentGA.addAggregate('STDDEV', 'sys_mod_count');
incidentGA.addAggregate('SUM', 'sys_mod_count');

incidentGA.query();

while (incidentGA.next()) {
gs.info('CATEGORY: ' + incidentGA.getValue('category'));
gs.info('AVG: ' + incidentGA.getAggregate('AVG', 'sys_mod_count'));
gs.info('COUNT: ' + incidentGA.getAggregate('COUNT', 'sys_mod_count'));
gs.info('GROUP_CONCAT: ' + incidentGA.getAggregate('GROUP_CONCAT', 'sys_mod_count'));
gs.info('GROUP_CONCAT_DISTINCT: ' + incidentGA.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count'));
gs.info('MAX: ' + incidentGA.getAggregate('MAX', 'sys_mod_count'));
gs.info('MIN: ' + incidentGA.getAggregate('MIN', 'sys_mod_count'));
gs.info('STDDEV: ' + incidentGA.getAggregate('STDDEV', 'sys_mod_count'));
gs.info('SUM: ' + incidentGA.getAggregate('SUM', 'sys_mod_count'));
gs.info(' ');
}

Output.

CATEGORY: inquiry
AVG: 8.42424242424242424242424242424242424242E00
COUNT: 33
GROUP_CONCAT: 0,0,0,0,1,2,2,4,4,4,5,5,5,6,6,6,6,6,6,6,6,7,8,8,8,8,9,15,15,19,32,33,36
GROUP_CONCAT_DISTINCT: 0,1,2,4,5,6,7,8,9,15,19,32,33,36
MAX: 36
MIN: 0
STDDEV: 9.15843294125113629932710147135171494439E00
SUM: 278
  
CATEGORY: software
AVG: 21
COUNT: 13
GROUP_CONCAT: 3,4,5,5,6,7,9,10,10,11,14,94,95
GROUP_CONCAT_DISTINCT: 3,4,5,6,7,9,10,11,14,94,95
MAX: 95
MIN: 3
STDDEV: 3.27693962918655755532970326989852794087E01
SUM: 273
  
CATEGORY: Hardware
AVG: 6.8
COUNT: 10
GROUP_CONCAT: 2,5,5,6,6,6,7,7,9,15
GROUP_CONCAT_DISTINCT: 2,5,6,7,9,15
MAX: 15
MIN: 2
STDDEV: 3.39280283999985933622820108982884699755E00
SUM: 68
  
CATEGORY: network
AVG: 18
COUNT: 5
GROUP_CONCAT: 3,12,17,21,37
GROUP_CONCAT_DISTINCT: 3,12,17,21,37
MAX: 37
MIN: 3
STDDEV: 1.25698050899765347157025586536136512302E01
SUM: 90
  
CATEGORY: 
AVG: 9.5
COUNT: 4
GROUP_CONCAT: 8,8,10,12
GROUP_CONCAT_DISTINCT: 8,10,12
MAX: 12
MIN: 8
STDDEV: 1.91485421551267621995020382273964310607E00
SUM: 38
  
CATEGORY: database
AVG: 29
COUNT: 2
GROUP_CONCAT: 8,50
GROUP_CONCAT_DISTINCT: 8,50
MAX: 50
MIN: 8
STDDEV: 2.969848480983499602483546320840365965E01
SUM: 58

Scoped equivalent

To use the addAggregate() method in a scoped application, use the corresponding scoped method: addAggregate().

GlideAggregate - addBizCalendarTrend(String fieldName, String bizCalendarSysId)

Adds trending by a business calendar to the aggregate query. This method allows you to pick a date and time field in the corresponding GlideRecord and group records based on a specified business calendar time span.

Note:

This method does not use the JOIN operator on the SQL queries.

Table 3. Parameters
Name	Type	Description
fieldName	String	Date and time field in the associated GlideRecord to use to determine in which group or calendar time span that the record will be included.
bizCalendarSysId	String	Sys_id of the calendar record to use. This is the calendar that contains the desired time spans.

Table 4. Returns
Type	Description
None

The following code example shows the count of incident records grouped by the “Month” business calendar time spans.

var monthCal = "4d7ddda353f3001076bcddeeff7b12b1"
var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.addBizCalendarTrend('opened_at', monthCal);
ga.setGroup(false);
ga.query();
gs.print(ga.getRowCount());
while (ga.next()) {
  gs.info(ga.getValue('bizcalref') + ', ' + ga.getValue('bizcalrefend') + ', ' + ga.getAggregate('COUNT'));
}

Output:

13
2015-08-01 00:00:00, 2015-09-01 00:00:00, 2
2015-11-01 00:00:00, 2015-12-01 00:00:00, 2
2016-08-01 00:00:00, 2016-09-01 00:00:00, 3
2016-12-01 00:00:00, 2017-01-01 00:00:00, 1
2018-08-01 00:00:00, 2018-09-01 00:00:00, 3
2018-09-01 00:00:00, 2018-10-01 00:00:00, 3
2018-10-01 00:00:00, 2018-11-01 00:00:00, 2
2019-07-01 00:00:00, 2019-08-01 00:00:00, 2
2020-06-01 00:00:00, 2020-07-01 00:00:00, 1
2021-01-01 00:00:00, 2021-02-01 00:00:00, 1
2023-04-01 00:00:00, 2023-05-01 00:00:00, 15
2023-05-01 00:00:00, 2023-06-01 00:00:00, 23
2023-07-01 00:00:00, 2023-08-01 00:00:00, 9

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	Encoded query string to add to the aggregate.

Table 6. Returns
Type	Description
None

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);
 
}

Scoped equivalent

To use the addEncodedQuery() method in a scoped application, use the corresponding scoped method: addEncodedQuery().

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

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

Table 7. Parameters
Name	Type	Description
name	String	Aggregate to filter on. For example, COUNT.
operator	String	Operator symbol. For example <, >, =, !=.
value	String	Value to query on. For example, '5'.

Table 8. Returns
Type	Description
None

var trend = new GlideAggregate('incident');
trend.addTrend ('opened_at','Month');
trend.addAggregate('COUNT');

//addHaving limits the results returned to those in which the aggregate COUNT is greater than 2
trend.addHaving('COUNT', '>', '2');
trend.setGroup(false);
trend.query();
while(trend.next()) {
  gs.print(('Incidents by month ' + trend.getValue('timeref') + ' where count is more than 2 count is: ' + trend.getAggregate('COUNT'));
}

Output:

Incidents by month 9/2018 where count is more than 2 count is: 3
Incidents by month 10/2018 where count is more than 2 count is: 8
Incidents by month 11/2018 where count is more than 2 count is: 14

GlideAggregate - addTrend(String fieldName, String timeInterval, Number numUnits)

Adds a trend for a field. Use a trend to show patterns over a period of time.

Note:

To control whether to group dayofweek results by year, use GlideAggregate - setIntervalYearIncluded(Boolean b).

Table 9. Parameters
Name	Type	Description
fieldName	String	Name of the field for which trending should occur.
timeInterval	String	Time interval for the trend. Valid values: date dayofweek hour minute quarter value week year
numUnits	Number	Optional. Only valid when timeInterval = minute. Number of minutes to include in the trend. Default: 1

Table 10. Returns
Type	Description
None

var trend = new GlideAggregate('incident');  
trend.addTrend ('opened_at','month');  
trend.addAggregate('COUNT');  
trend.setGroup(false);  
trend.query();  
while(trend.next()) {  
   gs.print(trend.getValue('timeref') + ': ' + trend.getAggregate('COUNT'));  
}

Output:

9/2018: 3
10/2018: 8
11/2018: 14

Scoped equivalent

To use the addTrend() method in a scoped application, use the corresponding scoped method: addTrend().

GlideAggregate - getAggregate(String agg, String name)

Gets the value of an aggregate from the current record.

Table 11. Parameters
Name	Type	Description
agg	String	Type of the aggregate. Valid values: AVG: Average value of the expression. COUNT: Count of the number of non-null values. GROUP_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma (','), and returns the result as a String. GROUP_CONCAT_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma (','), and returns the result as a String. MAX: Largest, or maximum, value. MIN: Minimum value. STDDEV: Population standard deviation. SUM: Sum of all values.
name	String	Name of the field to get the aggregate from.

Table 12. Returns
Type	Description
String	Value of the aggregation. If the values being aggregated are FX Currency values, the returned value is in the format `<currency_code;currency_value>`, such as: `USD;134.980000`. Note: If the specified field contains FX Currency values of mixed currency types, the method is not able to aggregate the values and returns a semicolon (;).

This example shows how to obtain the COUNT aggregate.

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; 
}

This example shows the aggregation of an FX Currency field.

var ga = new GlideAggregate('laptop_tracker');
ga.addAggregate('SUM', 'cost');
ga.groupBy('name');
ga.query();
while (ga.next()) {
  gs.info('Aggregate results ' + ga.getValue('name') + ' => ' + ga.getAggregate('SUM', 'cost'));
}

Output:

*** Script: Aggregate results Apple MacBook Air => USD;1651.784280000000
*** Script: Aggregate results Apple MacBook Pro => USD;1651.784280000000
*** Script: Aggregate results Dell XPS => USD;470.852672000000
*** Script: Aggregate results LG => 
*** Script: Aggregate results Samsung Galaxy => USD;225.320000000000
*** Script: Aggregate results Surface3 => USD;2895.560369520000
*** Script: Aggregate results Toshiba => USD;9385.202875800000

Scoped equivalent

To use the getAggregate() method in a scoped application, use the corresponding scoped method: getAggregate().

GlideAggregate - getQuery()

Retrieves the query necessary to return the current aggregate.

Table 13. Parameters
Name	Type	Description
None

Table 14. 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 - getRowCount()

Retrieves the number of rows in the GlideAggregate object.

Table 15. Parameters
Name	Type	Description
None

Table 16. Returns
Type	Description
Number	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 equivalent

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

GlideAggregate - getTotal(String agg, String name)

Returns the number of records by summing an aggregate.

Table 17. Parameters
Name	Type	Description
agg	String	Name of an aggregate to use. Valid values: AVG: Average value of the expression. COUNT: Count of the number of non-null values. GROUP_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma (','), and returns the result as a String. GROUP_CONCAT_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma (','), and returns the result as a String. MAX: Largest, or maximum, value. MIN: Minimum value. STDDEV: Population standard deviation. SUM: Sum of all values.
name	String	Name of the field to aggregate.

Table 18. Returns
Type	Description
Number	Number of records.

var incidentGA = new GlideAggregate('incident');
incidentGA.addQuery('category', 'software'); 
incidentGA.addAggregate('COUNT');  
incidentGA.addTrend('opened_at','year'); // Counting number of incidents for software category per year
incidentGA.setGroup(false);
incidentGA.query();
while(incidentGA.next()){
  gs.info('Incidents opened on year - '+incidentGA.getValue('timeref')+' - '+incidentGA.getAggregate('COUNT'));
}
gs.info('Total Aggregate Value >> '+ incidentGA.getTotal('COUNT'));

Output:

Incidents opened on year - 2015 - 1
Incidents opened on year - 2018 - 5
Incidents opened on year - 2020 - 10
Total Aggregate Value >> 16

GlideAggregate - getValue(String name)

Returns the value of a field.

Table 19. Parameters
Name	Type	Description
name	String	Name of the field.

Table 20. Returns
Type	Description
String	Value of the specified field. Returns null if invalid (not part of result set).

var trend = new GlideAggregate('incident');
trend.addTrend ('opened_at','Month');
trend.addAggregate('COUNT');

//addHaving limits the results returned to those in which the aggregate COUNT is greater than 2
trend.addHaving('COUNT', '>', '2');
trend.setGroup(false);
trend.query();
while(trend.next()) {
  gs.print(('Incidents by month ' + trend.getValue('timeref') + ' where count is more than 2 count is: ' + trend.getAggregate('COUNT'));
}

Output:

Incidents by month 9/2018 where count is more than 2 count is: 3
Incidents by month 10/2018 where count is more than 2 count is: 8
Incidents by month 11/2018 where count is more than 2 count is: 14

Scoped equivalent

To use the getValue() method in a scoped application, use the corresponding scoped method: getValue().

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 21. Parameters
Name	Type	Description
name	String	Name of the field.

Table 22. Returns
Type	Description
None

The following example shows how to call this method.

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);
}

Scoped equivalent

To use the groupBy() method in a scoped application, use the corresponding scoped method: groupBy().

GlideAggregate - orderBy(String name)

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

Table 23. Parameters
Name	Type	Description
name	String	Name of the field used to order the aggregates. Alternatively, you can provide a glidefunction to order the aggregates, such as `glidefunction:length(short_description)`. For more information about glidefunctions, see glidefunction operations.

Table 24. Returns
Type	Description
None

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);
}

Scoped equivalent

To use the orderBy() method in a scoped application, use the corresponding scoped method: orderBy().

GlideAggregate - orderByAggregate(String agg, String fieldName)

Orders the aggregates based on the specified aggregate and field.

Table 25. Parameters
Name	Type	Description
agg	String	Type of aggregation. Valid values: AVG: Average value of the expression. COUNT: Count of the number of non-null values. GROUP_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma (','), and returns the result as a String. GROUP_CONCAT_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma (','), and returns the result as a String. MAX: Largest, or maximum, value. MIN: Minimum value. STDDEV: Population standard deviation. SUM: Sum of all values.
fieldName	String	Name of the field to aggregate.

Table 26. Returns
Type	Description
None

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);
 
}

Scoped equivalent

To use the orderByAggregate() method in a scoped application, use the corresponding scoped method: orderByAggregate().

GlideAggregate - query()

Issues the query and gets the results.

Table 27. Parameters
Name	Type	Description
None

Table 28. Returns
Type	Description
None

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);

Scoped equivalent

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

GlideAggregate - setAggregateWindow(Number firstRow, Number lastRow)

Limits the number of rows from the table to include in the aggregate query.

Table 29. Parameters
Name	Type	Description
firstRow	Number	Zero-based index of the first row to include in the aggregate query, inclusive.
lastRow	Number	Zero-based index of the last row to include in the aggregate query, exclusive.

Table 30. Returns
Type	Description
None

Prints the count of each category for the first ten records in the Incident [incident] table.

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addAggregate('COUNT', 'category');
incidentGroup.setAggregateWindow(0, 10);
incidentGroup.query();
while (incidentGroup.next()) {
   var incidentCount = incidentGroup.getAggregate('COUNT', 'category');
   gs.info('{0} count: {1}', [incidentGroup.getValue('category'), incidentCount]);
}

Output:

database count: 1
Hardware count: 1
inquiry count: 7
software count: 1

Scoped equivalent

To use the setAggregateWindow() method in a scoped application, use the corresponding scoped method: setAggregateWindow().

GlideAggregate - setGroup(Boolean b)

Sets whether to group the results.

Table 31. Parameters
Name	Type	Description
b	Boolean	Flag that indicates whether to group the results. Valid values: true: Group the results. false: Do not group the results.

Table 32. Returns
Type	Description
None

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

Scoped equivalent

To use the setGroup() method in a scoped application, use the corresponding scoped method: setGroup().

GlideAggregate - setIntervalYearIncluded(Boolean b)

Sets whether to group results by year for day-of-week trends. These trends are created using the addTrend() method with the dayofweek time interval.

Dependency: GlideAggregate - addTrend('<fieldName>', 'dayofweek').

Table 33. Parameters
Name	Type	Description
b	Boolean	Flag that indicates whether to include a year for a trend with a day-of-week time interval. Valid values: true: Group day-of-week results by year. false: Exclude the year from time interval results. Default: true

Table 34. Returns
Type	Description
None

The following example shows how to count incidents created in the last six months. The incidents are separated by the day of the week, but not including the year. For example, the default results for Thursday would include the year, such as Thursday/2023: 16.

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addEncodedQuery("sys_created_onRELATIVEGT@month@ago@6");
incidentGroup.addTrend('sys_created_on', 'dayofweek');
incidentGroup.addAggregate('COUNT');
incidentGroup.setIntervalYearIncluded(false);
incidentGroup.query();
while (incidentGroup.next()) {
    gs.info(incidentGroup.getValue('timeref') + ': ' + incidentGroup.getAggregate('COUNT'))};

Output:

Sunday: 1
Monday: 15
Tuesday: 1
Wednesday: 7
Thursday: 16
Saturday: 1

Scoped equivalent

To use the setIntervalYearIncluded() method in a scoped application, use the corresponding scoped method: setIntervalYearIncluded().