How to get the MIN, MAX, AVG and SUM values from a table using the GlideAggregate function - Support and Troubleshooting

How to get the MIN, MAX, AVG and SUM values from a table using the GlideAggregate function Summary The following sample functions allow you to easily get the minimum, maximum, average, and sum of values from a table field.

Unexpected Results from GlideAggregate By default, the GlideAggregate function will perform a GROUP BY and ORDER BY on the aggregate fields specified. For example, if I wanted to get the most recent (MAX) Created date value for active incidents, I might think I could run the following GlideAggregate 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')); } Which shows me the following output:

*** Script: Most recent Incident Created Date = 2014-03-10 20:43:10 I know this is incorrect, I know I have more recent incidents created, so what's happening?

If I enable SQL debug (System Diagnostics > Session Debug > Debug SQL), I can see that the query generated is the following:

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` It is getting the maximum Created date value, but it's doing so for each distinct Created date value! This could end up being quite a lot of distinct date values and end up returning a lot of rows. In fact, if I remove the IF and replace it with a WHILE, I can get a better understanding of what's happening:

*** 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 Because of the GROUP BY and ORDER BY, I'm getting an unwanted grouping of the field. If I wanted to get the real MAX value that I'm looking for, I'd have to iterate through the GlideAggregate resultset and save the last value. This isn't ideal, since there could be a lot of records in that resultset that would take up memory and time. For my use case, the GROUP BY and the ORDER BY are unnecessary. What I really want is to run a query like this:

SELECT MAX(sys_created_on) FROM task WHERE task0.`sys_class_name` = 'incident' AND task0.`active` = 1; Disabling GROUP BY and ORDER BY The GROUP BY and ORDER BY can be disabled 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 For example, I can rewrite my code as:

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')); } And the query that is run has no GROUP BY nor ORDER BY clause in it:

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 And I get the results I expected:

*** Script: Most recent Incident Created Date = 2020-04-15 04:49:39 And it's only a single row returned from the database to the application server, so not a lot of memory used storing a large resultset!

Helper Functions The following helper functions can be used to easily get minimum, maximum, average and total/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'); } Use Cases / Example Output Here are a few example use cases:

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

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

// Get the average processing duration for events in the default event queue gs.print(getAvg('sysevent', 'processing_duration', 'queue=NULL')); References GlideAggregate https://developer.servicenow.com/dev.do#!/reference/api/orlando/server_legacy/c_GlideAggregateAPI