How to get MIN, MAX, AVG, and SUM values using GlideAggregate - Support and Troubleshooting

How to get MIN, MAX, AVG, and SUM values using GlideAggregate Summary .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Learn how to use the GlideAggregate function to retrieve minimum, maximum, average, and sum values from table fields. This article explains the default GROUP BY behavior that causes unexpected results and provides helper functions to simplify aggregate queries

Unexpected Results from GlideAggregate By default, GlideAggregate performs a GROUP BY and ORDER BY on the aggregate fields specified. This behavior can cause unexpected results.

Example scenario

To get the most recent (MAX) Created date value for active incidents, you might use the following code:

var ga = new GlideAggregate('incident'); ga.addQuery('active=1'); ga.addAggregate('MAX', 'sys_created_on'); ga.query(); if (ga.next()) { gs.log('Most recent Incident Created Date = ' + ga.getAggregate('MAX', 'sys_created_on')); } This code returns the following output:

*** Script: Most recent Incident Created Date = 2014-03-10 20:43:10 This result is incorrect because more recent incidents exist. The issue becomes clear when you enable SQL debug ( System Diagnostics > Session Debug > Debug SQL ) and view the generated query:

SELECT task0.`sys_created_on` AS `sys_created_on`, MAX(UNIX_TIMESTAMP(task0.`sys_created_on`) - UNIX_TIMESTAMP(19700101)) as max_of_970911560 FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND task0.`active` = 1 GROUP BY task0.`sys_created_on` ORDER BY task0.`sys_created_on` The query returns the maximum Created date value for each distinct Created date value, not a single maximum across all records. This can return many rows.

Replacing the IF statement with a WHILE loop demonstrates the issue:

*** Script: Most recent Incident Created Date = 2014-03-10 20:43:10 *** Script: Most recent Incident Created Date = 2015-08-25 23:41:54 *** Script: Most recent Incident Created Date = 2015-11-02 20:49:08 ... *** Script: Most recent Incident Created Date = 2020-04-14 20:48:46 *** Script: Most recent Incident Created Date = 2020-04-14 20:53:47 *** Script: Most recent Incident Created Date = 2020-04-15 04:49:39 The GROUP BY and ORDER BY clauses cause unwanted grouping. To get the actual MAX value, you would need to iterate through the entire result set and save the last value. This approach is inefficient because the result set could contain many records, consuming memory and time.

The desired query is:

SELECT MAX(sys_created_on) FROM task WHERE task0.`sys_class_name` = 'incident' AND task0.`active` = 1; Disable GROUP BY and ORDER BY Disable the GROUP BY and ORDER BY clauses using the following GlideAggregate methods:

setGroup(boolean) - Sets whether the results are to be grouped setOrder(boolean) - Sets whether the results are to be sorted Example

Rewrite the code to disable grouping and sorting:

var ga = new GlideAggregate('incident'); ga.addQuery('active=1'); ga.addAggregate('MAX', 'sys_created_on'); ga.setGroup(false); ga.setOrder(false); ga.query(); while (ga.next()) { gs.log('Most recent Incident Created Date = ' + ga.getAggregate('MAX', 'sys_created_on')); } The generated query no longer includes GROUP BY or ORDER BY clauses:

SELECT MAX(UNIX_TIMESTAMP(task0.`sys_created_on`) - UNIX_TIMESTAMP(19700101)) as max_of_970911560 FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND task0.`active` = 1 The query returns the expected result:

*** Script: Most recent Incident Created Date = 2020-04-15 04:49:39 This approach returns only a single row from the database, reducing memory usage and improving performance.

Helper Functions Use the following helper functions to retrieve minimum, maximum, average, and sum values:

function getMin(pTable, pColumn, pEncodedQuery) { return getAggregateByType(pTable, pColumn, pEncodedQuery, 'MIN'); }

function getMax(pTable, pColumn, pEncodedQuery) { return getAggregateByType(pTable, pColumn, pEncodedQuery, 'MAX'); }

function getAvg(pTable, pColumn, pEncodedQuery) { return getAggregateByType(pTable, pColumn, pEncodedQuery, 'AVG'); }

function getSum(pTable, pColumn, pEncodedQuery) { return getAggregateByType(pTable, pColumn, pEncodedQuery, 'SUM'); } Example use cases Example 1:

// Get the last time the admin user logged in gs.print(getMax('sys_user', 'last_login', 'user_name=admin'));

Example 2:

/ / Get the created date of the oldest active incident gs.print(getMax('incident', 'sys_created_on', 'active=1'));

Example 3:

// Get the average processing duration for events in the default event queue gs.print(getAvg('sysevent', 'processing_duration', 'queue=NULL'));

Note : The second example uses getMin (not getMax) to retrieve the oldest incident, as the minimum sys_created_on value represents the earliest creation date.

Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All supported releases

Related Links .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } GlideAggregate (ServiceNow developer documentation)