GlideAggregate in ServiceNow: The Complete Developer Guide

What is GlideAggregate?

GlideAggregate is a server-side ServiceNow API that extends GlideRecord to support SQL aggregate functions. The key difference between GlideRecord and GlideAggregate is architectural: GlideRecord loads record data into the application server's memory so your script can work with it. GlideAggregate sends the aggregation instruction to the database engine and only brings back the result — a single number, or a small result set of grouped numbers.

In practical terms: if you have a table with 500,000 incident records and you want to know how many are active, GlideRecord would load all 500,000 records into memory and count them in your script. GlideAggregate asks the database "how many active incidents are there?" and returns a single integer. The performance difference on large tables is not marginal — it is several orders of magnitude faster.

GlideAggregate supports five aggregate functions:

• COUNT — the number of records matching the query
• SUM — the total of all values in a numeric field
• AVG — the average of all values in a numeric field
• MIN — the smallest value in a field across matching records
• MAX — the largest value in a field across matching records

It also supports GROUP BY (break down counts by a category field), HAVING (filter groups by aggregate values), and ORDER BY (sort grouped results). Everything you would write in SQL, you can do with GlideAggregate.

The performance case — GlideAggregate vs GlideRecord

Before diving into syntax, it is worth internalising why this matters. Consider this common pattern written the wrong way:

// Wrong — loads every matching record into memory
var count = 0;
var gr = new GlideRecord('incident');
gr.addEncodedQuery('active=true^state=1');
gr.query();
while (gr.next()) {
    count++;
}
gs.log('Count: ' + count);

On a large production table, this script loads every record that matches the query into the application server's memory. If 10,000 incidents match, you are allocating memory for 10,000 records just to count them. If this runs in a Business Rule that fires on every incident update, you are doing this on every save.

The correct version:

// Right — asks the database to count, returns one number
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^state=1');
ga.addAggregate('COUNT');
ga.query();
if (ga.next()) {
    gs.log('Count: ' + ga.getAggregate('COUNT'));
}

The database returns a single integer. No record data crosses into the application server. The performance difference at scale is the difference between a script that completes in milliseconds and one that causes noticeable instance slowness. This is covered in more detail in our guide to GlideRecord performance tips — GlideAggregate is the primary tool for the single most common performance anti-pattern on the platform.

Basic COUNT usage

The simplest use case — counting records that match a condition:

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.query();
if (ga.next()) {
    var total = ga.getAggregate('COUNT');
    gs.log('Total incidents: ' + total);
}

Adding a filter with addEncodedQuery():

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^state=1');
ga.addAggregate('COUNT');
ga.query();
if (ga.next()) {
    var openActive = ga.getAggregate('COUNT');
    gs.log('Open active incidents: ' + openActive);
}

You can also use individual addQuery() calls instead of an encoded query string. Both approaches produce the same SQL. For guidance on encoded query syntax, see the complete guide to GlideRecord encoded queries.

var ga = new GlideAggregate('incident');
ga.addQuery('active', true);
ga.addQuery('state', '1');
ga.addAggregate('COUNT');
ga.query();
if (ga.next()) {
    gs.log('Count: ' + ga.getAggregate('COUNT'));
}

SUM — totalling a numeric field

SUM works on any numeric field. A common use case is totalling estimated durations, costs, or other numeric values across a set of records:

var ga = new GlideAggregate('change_request');
ga.addEncodedQuery('state=implement^active=true');
ga.addAggregate('SUM', 'estimated_duration');
ga.query();
if (ga.next()) {
    var totalHours = ga.getAggregate('SUM', 'estimated_duration');
    gs.log('Total estimated hours for active changes: ' + totalHours);
}

Note that when you use SUM, AVG, MIN, or MAX, you pass the field name as the second argument to addAggregate(). When using COUNT without grouping, you do not need to pass a field name — COUNT counts rows, not values.

A practical example — summing story points across all open enhancements in a sprint:

var ga = new GlideAggregate('rm_story');
ga.addEncodedQuery('sprint=^state!=complete');
ga.addAggregate('SUM', 'story_points');
ga.query();
if (ga.next()) {
    var remaining = ga.getAggregate('SUM', 'story_points');
    gs.log('Remaining story points: ' + remaining);
}

AVG — calculating averages

AVG returns the mean value across all matching records for a given numeric field. Useful for SLA reporting, performance metrics, and dashboards:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('closed_atONThis quarter@javascript:gs.beginningOfThisQuarter()@javascript:gs.endOfThisQuarter()');
ga.addAggregate('AVG', 'reassignment_count');
ga.query();
if (ga.next()) {
    var avgReassignments = ga.getAggregate('AVG', 'reassignment_count');
    gs.log('Average reassignments this quarter: ' + avgReassignments);
}

A note on null values: AVG ignores null values in the calculation. If a field is empty for some records, those records are excluded from the average — which is standard SQL behaviour.

MIN and MAX

MIN and MAX return the smallest and largest values in a field across matching records. Useful for finding the earliest or latest date, the smallest or largest number, or the first or last value alphabetically:

// Find the earliest open high-priority incident
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^priority=1');
ga.addAggregate('MIN', 'opened_at');
ga.query();
if (ga.next()) {
    var oldest = ga.getAggregate('MIN', 'opened_at');
    gs.log('Oldest open P1: ' + oldest);
}

// Find the highest-priority open incident number
var ga2 = new GlideAggregate('incident');
ga2.addEncodedQuery('active=true');
ga2.addAggregate('MIN', 'priority');
ga2.query();
if (ga2.next()) {
    gs.log('Highest priority: ' + ga2.getAggregate('MIN', 'priority'));
}

Combining multiple aggregates in one query

You can call addAggregate() multiple times to request multiple aggregate values in a single database query — more efficient than running separate queries:

var ga = new GlideAggregate('change_request');
ga.addEncodedQuery('active=true');
ga.addAggregate('COUNT');
ga.addAggregate('SUM', 'estimated_duration');
ga.addAggregate('AVG', 'estimated_duration');
ga.addAggregate('MIN', 'estimated_duration');
ga.addAggregate('MAX', 'estimated_duration');
ga.query();
if (ga.next()) {
    gs.log('Count: ' + ga.getAggregate('COUNT'));
    gs.log('Total hours: ' + ga.getAggregate('SUM', 'estimated_duration'));
    gs.log('Average hours: ' + ga.getAggregate('AVG', 'estimated_duration'));
    gs.log('Min hours: ' + ga.getAggregate('MIN', 'estimated_duration'));
    gs.log('Max hours: ' + ga.getAggregate('MAX', 'estimated_duration'));
}

One database round trip, five aggregate values. This is the pattern to use when building dashboard widgets or summary reports.

GROUP BY — breaking down counts by category

GROUP BY is where GlideAggregate becomes truly powerful for reporting. It lets you count (or sum, or average) records broken down by the values in a category field — the equivalent of a pivot table.

Count incidents by category:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true');
ga.addAggregate('COUNT', 'category');
ga.groupBy('category');
ga.orderBy('category');
ga.query();
while (ga.next()) {
    var category = ga.category.getDisplayValue();
    var count = ga.getAggregate('COUNT', 'category');
    gs.log(category + ': ' + count);
}

Count incidents by assignment group:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^state=1');
ga.addAggregate('COUNT', 'assignment_group');
ga.groupBy('assignment_group');
ga.orderByAggregate('COUNT', 'assignment_group'); // order by count descending
ga.query();
while (ga.next()) {
    var group = ga.assignment_group.getDisplayValue();
    var count = ga.getAggregate('COUNT', 'assignment_group');
    gs.log(group + ': ' + count + ' open incidents');
}

A few important notes on GROUP BY:

• You use while (ga.next()) with GROUP BY, not if (ga.next()), because there is one result row per group
• Use ga.fieldName.getDisplayValue() to get the human-readable label for reference fields (like assignment_group) — ga.getValue('assignment_group') returns the sys_id
• You can group by multiple fields by calling groupBy() multiple times

GROUP BY with multiple fields

Break down counts by two dimensions simultaneously:

// Incidents by category AND priority
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true');
ga.addAggregate('COUNT');
ga.groupBy('category');
ga.groupBy('priority');
ga.orderBy('priority');
ga.orderBy('category');
ga.query();
while (ga.next()) {
    var cat = ga.getValue('category');
    var pri = ga.getValue('priority');
    var count = ga.getAggregate('COUNT');
    gs.log('P' + pri + ' ' + cat + ': ' + count);
}

This produces output like "P1 network: 3", "P1 software: 7", "P2 network: 12" — useful for building heatmaps or multi-dimensional reports.

ORDER BY on aggregates

Two ordering methods work with GlideAggregate:

// Order by a field value (alphabetical, numeric)
ga.orderBy('category');
ga.orderByDesc('category'); // descending

// Order by the aggregate value itself
ga.orderByAggregate('COUNT', 'category');
ga.orderByAggregateDesc('COUNT', 'category'); // most-to-least common

Ordering by the aggregate value descending is the most common pattern for "top N" reports — showing the most common categories, the assignment groups with the most open work, or the users who have submitted the most requests.

HAVING — filtering groups by aggregate values

HAVING filters the grouped results after aggregation. The difference between addQuery() and addHaving(): addQuery filters individual records before aggregation; addHaving filters groups after aggregation.

Only show assignment groups with more than 10 open incidents:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true');
ga.addAggregate('COUNT', 'assignment_group');
ga.groupBy('assignment_group');
ga.addHaving('COUNT', 'assignment_group', '>', 10);
ga.orderByAggregateDesc('COUNT', 'assignment_group');
ga.query();
while (ga.next()) {
    var group = ga.assignment_group.getDisplayValue();
    var count = ga.getAggregate('COUNT', 'assignment_group');
    gs.log(group + ': ' + count + ' open incidents');
}

HAVING supports the same comparison operators as addQuery: >, <, >=, <=, =, !=. It is particularly useful for:

• Finding overloaded queues (groups with more than X items)
• Identifying high-volume patterns (users who have submitted more than Y requests)
• Filtering out low-sample-size groups from averages

Using setLimit() with GlideAggregate

When you want only the top N results from a grouped query, use setLimit():

// Top 5 assignment groups by open incident count
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true');
ga.addAggregate('COUNT', 'assignment_group');
ga.groupBy('assignment_group');
ga.orderByAggregateDesc('COUNT', 'assignment_group');
ga.setLimit(5);
ga.query();
while (ga.next()) {
    gs.log(ga.assignment_group.getDisplayValue() + ': ' + ga.getAggregate('COUNT', 'assignment_group'));
}

Distinct count with addAggregate

To count distinct values rather than all records, use setDistinct(true):

// Count unique users who submitted an incident this month
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('opened_atONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()');
ga.addAggregate('COUNT', 'caller_id');
ga.setDistinct(true);
ga.query();
if (ga.next()) {
    gs.log('Unique callers this month: ' + ga.getAggregate('COUNT', 'caller_id'));
}

Real production patterns

Pattern 1 — Dashboard widget data

This is the most common production use of GlideAggregate. A Script Include that returns summary counts for a homepage widget:

var IncidentSummaryUtils = Class.create();
IncidentSummaryUtils.prototype = {
    initialize: function() {},

    getOpenCountByPriority: function() {
        var result = {};
        var ga = new GlideAggregate('incident');
        ga.addEncodedQuery('active=true');
        ga.addAggregate('COUNT', 'priority');
        ga.groupBy('priority');
        ga.orderBy('priority');
        ga.query();
        while (ga.next()) {
            var pri = ga.getValue('priority');
            result['P' + pri] = parseInt(ga.getAggregate('COUNT', 'priority'));
        }
        return result;
    },

    getTotalOpenCount: function() {
        var ga = new GlideAggregate('incident');
        ga.addEncodedQuery('active=true');
        ga.addAggregate('COUNT');
        ga.query();
        if (ga.next()) {
            return parseInt(ga.getAggregate('COUNT'));
        }
        return 0;
    },

    type: 'IncidentSummaryUtils'
};

Calling this from a Business Rule or UI script gives you a clean, performance-safe way to build dashboard data. For guidance on building Script Includes like this, see the complete guide to Script Includes in ServiceNow.

Pattern 2 — Conditional logic based on count

Use GlideAggregate when your script needs to branch based on how many records exist — without loading those records:

// In a Business Rule — check if a user already has open P1 incidents before creating another
var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^priority=1^caller_id=' + current.getValue('caller_id'));
ga.addAggregate('COUNT');
ga.query();
if (ga.next()) {
    var existingCount = parseInt(ga.getAggregate('COUNT'));
    if (existingCount >= 3) {
        current.setAbortAction(true);
        gs.addErrorMessage('You already have ' + existingCount + ' open P1 incidents. Please update an existing incident.');
    }
}

Pattern 3 — Scheduled report generation

Building a weekly summary email using GlideAggregate for all the counts:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('opened_atONLast 7 days@javascript:gs.beginningOfLast7Days()@javascript:gs.endOfLast7Days()');
ga.addAggregate('COUNT');
ga.addAggregate('COUNT', 'priority');
ga.groupBy('priority');
ga.query();

var summary = 'Weekly Incident Summary\n';
var total = 0;
while (ga.next()) {
    var pri = ga.getValue('priority');
    var count = parseInt(ga.getAggregate('COUNT', 'priority'));
    total += count;
    summary += 'P' + pri + ': ' + count + '\n';
}
summary += 'Total: ' + total;
gs.log(summary);

Pattern 4 — Checking existence before a costly operation

When you only need to know if any records exist — not how many — COUNT with a limit of 1 is faster than a full GlideRecord query:

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^caller_id=' + userId + '^priority=1');
ga.addAggregate('COUNT');
ga.setLimit(1);
ga.query();
if (ga.next() && parseInt(ga.getAggregate('COUNT')) > 0) {
    // User has at least one open P1 — take action
}

GlideAggregate with date functions

ServiceNow provides built-in JavaScript date functions you can use inside encoded queries with GlideAggregate. These are especially useful for time-period reports:

// This week
ga.addEncodedQuery('opened_atONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()');

// Last month
ga.addEncodedQuery('opened_atONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()');

// This quarter
ga.addEncodedQuery('opened_atONThis quarter@javascript:gs.beginningOfThisQuarter()@javascript:gs.endOfThisQuarter()');

// Last 30 days
ga.addEncodedQuery('opened_atONLast 30 days@javascript:gs.daysAgoStart(30)@javascript:gs.daysAgoEnd(0)');

Common mistakes and how to avoid them

Mistake 1 — Using getAggregate() with wrong arguments

When you use addAggregate('COUNT') without a field name (plain count), retrieve it with getAggregate('COUNT') — no field argument. When you use addAggregate('COUNT', 'category') with a field name (grouped count), retrieve it with getAggregate('COUNT', 'category') — matching field argument.

// Plain count — no field argument
ga.addAggregate('COUNT');
ga.getAggregate('COUNT'); // correct
ga.getAggregate('COUNT', 'category'); // wrong — returns null

// Grouped count — requires field argument
ga.addAggregate('COUNT', 'category');
ga.groupBy('category');
ga.getAggregate('COUNT', 'category'); // correct
ga.getAggregate('COUNT'); // wrong — returns null

Mistake 2 — Using if instead of while with GROUP BY

// Wrong — only processes first group
if (ga.next()) { ... }

// Right — processes all groups
while (ga.next()) { ... }

Mistake 3 — Not parsing the return value

getAggregate() returns a string, not a number. If you need to do arithmetic with the result, parse it first:

var count = parseInt(ga.getAggregate('COUNT')); // number
var total = parseFloat(ga.getAggregate('SUM', 'estimated_duration')); // decimal

Mistake 4 — Forgetting to call query()

Like GlideRecord, GlideAggregate does not execute until you call ga.query(). Building up the aggregate and groupBy calls first, then calling query once at the end, is the correct pattern.

GlideAggregate vs GlideRecord — the decision rule

The rule is simple and absolute:

• Use GlideAggregate when you need a count, sum, average, min, or max — any time you would otherwise loop through records just to calculate a number
• Use GlideRecord when you need to access, read, update, or delete individual record data

There is no case where GlideRecord is the better choice for counting. If you find yourself writing var count = 0; while (gr.next()) { count++; }, replace it with GlideAggregate immediately.

For more on GlideRecord query optimisation — including chooseWindow, addFieldToSelect, setLimit, and other patterns — see the GlideRecord performance tips guide. For a broader look at all the GlideRecord query operators and encoded query syntax, see the encoded queries complete guide.

GlideAggregate in scoped applications

GlideAggregate works in scoped applications without any special configuration. The same ACL rules that govern GlideRecord table access apply — if your application scope does not have read access to a table, GlideAggregate will not return results from it. For guidance on scoped app architecture, see the guide to scoped applications in ServiceNow.

Performance characteristics and limits

GlideAggregate is fast, but it is not magic. A few things to be aware of:

• Large GROUP BY result sets can be slow — if your groupBy field has thousands of distinct values (like user sys_ids on a large incident table), the result set is large and the query may be slow. Add a HAVING clause or a setLimit() to keep result sets manageable.
• Indexes matter — GlideAggregate queries benefit from the same database indexes as GlideRecord queries. If your encoded query filters on an unindexed field, performance will degrade on large tables. This is a platform administration concern, not a developer one — but it is worth knowing if a query is slow despite correct GlideAggregate usage.
• Date field aggregation can be expensive — MIN and MAX on date fields with large data sets can take longer than COUNT on an indexed field. If you are building a scheduled job that runs frequently, test the query on production-scale data first.

Quick reference

The most common GlideAggregate patterns in a single reference block:

var ga = new GlideAggregate('table_name');

// Filters (same as GlideRecord)
ga.addEncodedQuery('field=value^field2!=value2');
ga.addQuery('field', 'value');

// Aggregates
ga.addAggregate('COUNT');                    // row count
ga.addAggregate('COUNT', 'field');           // count per grouped field
ga.addAggregate('SUM', 'numeric_field');     // sum of field values
ga.addAggregate('AVG', 'numeric_field');     // average of field values
ga.addAggregate('MIN', 'field');             // minimum value
ga.addAggregate('MAX', 'field');             // maximum value

// Grouping
ga.groupBy('category');                      // group by field
ga.groupBy('priority');                      // multiple group-by fields

// Filtering groups
ga.addHaving('COUNT', 'field', '>', 10);    // filter after aggregation

// Ordering
ga.orderBy('field');                         // ascending by field
ga.orderByDesc('field');                     // descending by field
ga.orderByAggregate('COUNT', 'field');       // ascending by count
ga.orderByAggregateDesc('COUNT', 'field');   // descending by count

// Limits
ga.setLimit(10);                             // top 10 groups
ga.setDistinct(true);                        // count distinct values

// Execute
ga.query();

// Retrieve results
if (ga.next()) {                             // single result
    var val = ga.getAggregate('COUNT');      // get aggregate value
    var val2 = parseInt(val);               // parse to number
}
while (ga.next()) {                          // grouped results
    var group = ga.field.getDisplayValue();  // display value of grouped field
    var count = ga.getAggregate('COUNT', 'field');
}