How to count Distinct in GlideRecord?

[sarahm]

Hi Team,

I need to count the distinct number of requested approvals in the sysapproval_approver table.

Like as shown in the below screenshot, the total count of approvals in requested state is 60, but after GroupBy of the Approval For the result is 31. I am trying to achieve this by glideRecord but it's not working. Can you please assist.

                              var count = 0;

                               var gr = new GlideAggregate(‘sysapproval_approver’);

                               gr.addQuery (‘state’,’requested’);

                               gr.addAggregate('COUNT','sysapproval');

                               gr.orderByAggregate('COUNT','sysapproval');

                               gr.query();

                              if(gr.next())

                                count = gr.getAggregate('COUNT','sysapproval');

                               gs.print(parseInt(count));

 

Thanks in advance,

Sarah

[Anurag Tripathi]

Hey Sarah,

Check this post on SN Guru, explains with eg the same thing.

https://www.servicenowguru.com/scripting/gliderecord-distinct-query/

Please mark my answer correct/helpful if it helps you solve your issue.
-Anurag

[Alberto Consonn]

Hi,

try this code as example:

var gr = new GlideAggregate('cmdb_ci_server'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('os'); //Group aggregate by the 'os' field
gr.query();
while(gr.next()){
   var osCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
   gs.print('Distinct operating system: ' + gr.os + ': ' + osCount);
}

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

Thank you

Cheers
Alberto

[sarahm]

Thanks Alberto, I used the below code:

var count = 0;
var gr = new GlideAggregate('sysapproval_approver');
gr.addQuery (‘state’,’requested’);
gr.addAggregate('count');
gr.orderByAggregate('count');
gr.groupBy('sysapproval');
gr.query();
while(gr.next()) {
count = gr.getAggregate('count');
gs.print(gr.getDisplayValue('sysapproval') + " "+count);
}

I get the result as below:

However, I need the summation of the values as the result only..like 14+9+8...

Can you suggest?

Thanks,

Sarah

[Alberto Consonn]

Try with this code:

var count = 0;
var gr = new GlideAggregate('sysapproval_approver');
gr.addQuery (‘state’,’requested’);
gr.addAggregate('count');
gr.orderByAggregate('count');
gr.groupBy('sysapproval');
gr.query();
while(gr.next()) {
count = count + gr.getAggregate('count');
}
gs.print(count);

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

Thank you

Cheers
Alberto