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

[sarahm]

Thanks Alberto,

But this just appends the values instead of a summation.

Regards,

Sarah

[Alberto Consonn]

Sorry, try this one please:

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.getValue('count');
}
gs.print(count);

[sarahm]

Unfortunately Alberto this returns 0.

Regards,

Sarah

[Brian Lancaster]

If you look at the screenshot it still show 60 total right under where it says 1 to 31 of 31.  The 1 to 31 ot 31 is just display how they are being displayed as a group by.  But the total number of records will always be 60.

[Brian Lancaster · Accepted solution]

If you want the total number to be like when they are group by try using GlideRecord instead of GlideAggregate.

var count = 0;
var counted = [];
var gr = new GlideRecord('sysapproval_approver');
gr.addQuery ('state','requested');
gr.query();
while(gr.next()) {
	if (!counted[gr.sysapproval]){
		counted[gr.sysapproval] = true;
		count++;
		
	}
}
gs.print(count);