The Hidden COUNT(DISTINCT) Trick in GlideAggregate Most Developers Miss

[Astik Thombare]

In ServiceNow development, counting records is common.
But counting unique records correctly and efficiently is where many developers make mistakes.

This article explains:

• Why common approaches fail

• Why COUNT is not enough

• The hidden COUNT(DISTINCT capability in GlideAggregate

• The correct and scalable way to retrieve unique counts

This topic is frequently discussed in interviews and real-world development scenarios.

The Requirement

Scenario:
Find the number of unique callers in the Incident table.

Example:

Although Abel Tuter appears twice, he should only be counted once.

Expected Result: 2 unique callers

Method 1: Using List View (UI Verification)

From the platform UI:

• Navigate to Incident → List
• Right-click the Caller column
• Select Group By → Caller

This shows unique caller groupings.

However, this approach is limited to UI usage and cannot be used in:

• Business Rules

• Script Includes

• Scheduled Jobs

• Background Scripts

For automation and scalability, we need scripting.

Method 2: GlideRecord + ArrayUtil (Works but Not Scalable)

var callerArr = [];
var incGr = new GlideRecord('incident');
incGr.query();

while (incGr.next()) {
    callerArr.push(incGr.getDisplayValue('caller_id'));
}

var au = new ArrayUtil();
var uniqueCaller = au.unique(callerArr);

gs.print('Count Of Unique Records - ' + uniqueCaller.length);

Why This Is Not Recommended

• Iterates through all records

• Loads data into memory

• Poor performance on large tables

• Not aligned with best practices

This approach shifts processing from the database to the application layer.

Method 3: GlideAggregate with COUNT (Common Mistake)

Most developers correctly switch to GlideAggregate for counting:

var incGa = new GlideAggregate('incident');
incGa.addAggregate('COUNT', 'caller_id');
incGa.setGroup(false);
incGa.query();
incGa.next();

gs.print(incGa.getAggregate('COUNT', 'caller_id'));

The Problem

This returns total records (for example, 67).
It does NOT return unique callers (for example, 23).

Because:

COUNT counts rows — not distinct values.

The Hidden Solution: COUNT(DISTINCT)

There is an additional aggregate parameter available in GlideAggregate

COUNT(DISTINCT

Yes — it includes the opening parenthesis and does not include a closing one.

It is not widely documented, which is why many developers miss it.

Correct Syntax

ga.addAggregate('COUNT(DISTINCT', 'field_name');
ga.setGroup(false);

Working Example

var incGa = new GlideAggregate('incident');
incGa.addAggregate('COUNT(DISTINCT', 'caller_id');
incGa.setGroup(false);
incGa.query();
incGa.next();

gs.print(incGa.getAggregate('COUNT(DISTINCT', 'caller_id'));

Why Does This Work?

Aggregate values in ServiceNow are stored internally as choice values in:

sys_report.aggregate

The choice value for “Count Distinct” is:

Count(Distinct

 Just like a normal COUNT aggregate, you must use:

ga.setGroup(false);

Otherwise, grouped results will be returned instead of a single aggregate value. 

Real-World Example: Counting Distinct Users with Specific Roles

var ga = new GlideAggregate('sys_user_has_role');
ga.addQuery('user.active', true);
ga.addEncodedQuery("role.nameINitil,admin");

ga.addAggregate('COUNT(DISTINCT', 'user');
ga.setGroup(false);
ga.query();
ga.next();

var count = ga.getAggregate('COUNT(DISTINCT', 'user');
gs.print(count);

This returns the number of distinct active users who have either the itil or admin role.

Best Practice Summary

• Use GlideAggregate for record counting
• Avoid getRowCount() in production logic
• Avoid array-based deduplication for large datasets
• Use COUNT(DISTINCT for unique counts
• Always use setGroup(false) when expecting a single value

Conclusion

When working with large datasets in ServiceNow, performance matters.

Instead of iterating through records or performing memory-based deduplication, leverage database-level aggregation using:

COUNT(DISTINCT

This approach is:

• Efficient

• Scalable

• Clean

• Interview-relevant

• Aligned with ServiceNow best practices

Credits & References

This solution was originally discussed in ServiceNow Community by:

• Allen Andreas

• Travis Toulson

Reference Links:

1. https://www.servicenow.com/community/developer-articles/glideaggregate-get-unique-records-easily/ta-...

2. https://www.servicenow.com/community/developer-forum/count-distinct-in-glideaggregate/m-p/1721341/pa...

If you found this helpful, I regularly share ServiceNow development insights and best practices on LinkedIn.
You can follow and stay connected here:
https://www.linkedin.com/in/ath05/