Find the highest count of records grouped by category

[Trupti Krishnam]

I want to Find the highest count of records grouped by category .., for example the category is 'book' which has 40 records , Category is Pen that has 80 records , category is 'bag' that has 1000 records , category is 'laptop' = 80 the output should be like

category bag - 1000

category laptop  - 80

category pen - 80

category book - 40 

I have written a GlideAggregate Script 

var gr = new GlideAggregate('user');

gr.addEncodedQuery('active=true');

gr.addAggregate('COUNT');

gr.groupBy('category');

gr.orderByDesc('category');

gr.query();

while(gr.next()){

    var ga = gr.getAggregate('COUNT');

    gs.info('category:' +gr.getDisplayValue('category') + 'number is :' +ga);

}

but it's not sorting in the descending order 

Help me with the sample code

[Ankur Bawiskar]

@Trupti Krishnam 

GlideAggregate won't directly sort, you will have to enhance your script

this should work

var gr = new GlideAggregate('user');
gr.addEncodedQuery('active=true');
gr.addAggregate('COUNT');
gr.groupBy('category');
gr.query();

var results = [];

while (gr.next()) {
    var category = gr.getDisplayValue('category');
    var count = gr.getAggregate('COUNT');
    results.push({ category: category, count: parseInt(count) });
}

// Sort the results array in descending order by count
results.sort(function(a, b) {
    return b.count - a.count;
});

// Print the sorted results
results.forEach(function(result) {
    gs.info('category: ' + result.category + ' - ' + result.count);
});

I tried the same script for incident and priority and it worked fine

var gr = new GlideAggregate('incident');
gr.addEncodedQuery('active=true');
gr.addAggregate('COUNT');
gr.groupBy('priority');
gr.query();

var results = [];

while (gr.next()) {
    var category = gr.getDisplayValue('priority');
    var count = gr.getAggregate('COUNT');
    results.push({ category: category, count: parseInt(count) });
}

// Sort the results array in descending order by count
results.sort(function(a, b) {
    return b.count - a.count;
});

// Print the sorted results
results.forEach(function(result) {
    gs.info('category: ' + result.category + ' - ' + result.count);
});

Output:

If my response helped please mark it correct and close the thread so that it benefits future readers.

[J Siva]

Hi @Trupti Krishnam 
Try below script. It'll sort the output in the descending order.

var gr = new GlideAggregate('sys_user'); //Replace the table name
gr.addEncodedQuery('active=true^department!=NULL'); // replace "Department" with the actual field name
 gr.orderByAggregate('COUNT', 'department'); // replace "Department" with the actual field name
gr.addAggregate('COUNT','department');// replace "Department" with the actual field name
gr.groupBy('department');// replace "Department" with the actual field name
gr.query();
while(gr.next()){
   gs.info(gr.getDisplayValue('department') + ': ' + gr.getAggregate('COUNT', 'department'));// replace "Department" with the actual field name
}

Output:

Regards,
Siva

[Trupti Krishnam]

it's show as NAN 

[J Siva · Accepted solution]

@Trupti Krishnam Could you share your script?