Count Distinct in GlideAggregate

gyedwab · ‎05-04-2015

It looks like there's a "count distinct" available in Fuji GlideAggregate... does anyone know the syntax needed?

tltoulson · ‎05-04-2015

Hi Guy,

I don't see this documented anywhere but I figured it out:

var ga = new GlideAggregate('incident');


ga.addAggregate('COUNT(DISTINCT', 'state');


ga.setGroup(false);


ga.query();


ga.next();


gs.print(ga.getAggregate('COUNT(DISTINCT', 'state'));

Here's how I figured it out:

I assumed that Count Distinct worked the same as all the other aggregates. That is, they are stored with the sys_report record as a choice list. Each aggregate has the choice value and a choice label. We see the choice label but the system uses the choice value in scripts. So:

1. I opened the sys_dictionary entry for sys_report.aggregate

2. Look at the choices for the Aggregate field, and saw that Count Distinct used the value Count(Distinct (which was weird because I initially expected Count(Distinct))

3. I then assumed it followed the same behavior as the other field based aggregates and so use the same addAggregate function with the field as the second parameter

4. This gave me a list of records, so I assumed that Count Distinct requires setGroup to be false, the same as if you do a Count aggregate on all records

Tried this in a background script and it worked!

Hope this helps.

Kind regards,

Travis

View solution in original post

Victor Ruiz · ‎05-04-2015

Here's a good post: Scoped GlideAggregate API Reference

gyedwab · ‎05-04-2015

Hi Victor,

Thanks for the link... I saw this, but unless I overlooked something I didn't see anything about how to leverage "Distinct". Also, we're not currently using a scoped application.

Mike Allen · ‎05-04-2015

You can do a count on a field and do a groupBy on that field. That would give you a distinct. That is how I do it in list views, anyway.

Mike Allen · ‎05-04-2015

You could also do gs.sql().