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-p/2683384

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

 

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/