Count Distinct in GlideAggregate

[gyedwab]

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

[gyedwab]

Hi Mike,

The feedback we've gotten from the Technology Partnership Program is that they don't want to certify any functionality that leverages gs.sql().

The count + groupBy solution works in list views, but we need to get the value in order to manipulate it in a calculation... the way our integration is implemented, we would need access to count "distinct" to do it more cleanly, and we saw that the out-of-box reporting engine now supports that.

[tltoulson · Accepted solution]

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

[gyedwab]

Hey Travis,

This is exactly what I was looking for, thanks. I'll play around with it and come back if I have any questions, but it seems to do exactly what we were looking for... I never would have guessed Count(Distinct, and I appreciate you letting me know how you looked it up.