Group by Count Query

[Sunny14]

Hello Team,

I have below query to get the count of Total Tickets count Group by the Fulfiller. My question is, is it possible to get the Assignment Group Name they belong to in the result? If so how can I add that?

Please advise. Thanks.

var rec = new GlideAggregate('task');

rec.groupBy('assigned_to');

rec.addAggregate('COUNT');

rec.query();

while(rec.next()){

     var AT = rec.getDisplayValue('assigned_to');

     var Count = rec.getAggregate('COUNT');

     gs.print("Assigned To :: " + AT+ " Count :: "+Count ) ;

}

[Rajesh Chopade1]

Hi @Sunny14 

You can try bellow script

var rec = new GlideAggregate('task');
rec.groupBy('assigned_to'); // Group by assigned user
rec.addAggregate('COUNT');  // Get the ticket count
rec.query();

while (rec.next()) {
    var userID = rec.getValue('assigned_to');
    var userName = rec.getDisplayValue('assigned_to');
    var count = rec.getAggregate('COUNT');
    
    // Now, query the assignment groups that the user belongs to
    var group = new GlideRecord('sys_user_grmember');
    group.addQuery('user', userID); // Query for groups where the user is a member
    group.query();
    
    var groupNames = [];
    while (group.next()) {
        groupNames.push(group.getDisplayValue('group')); // Collect group names
    }
    
    gs.print("Assigned To: " + userName + " | Count: " + count + " | Groups: " + groupNames.join(', '));
}

[Sunny14]

Hello @Rajesh Chopade1  Thank you very much for your prompt response. 

I really liked your logic.

Is it possible to fine tune your logic to show Assignment Groups ONLY for which Fulfiller has worked on the Task [i.e. From the Task Table].

Currently the result is showing all the groups the Fulfiller belongs to regardless of if Ticket was assigned to that Group or not.

Please advise. 

Thanks. 

[Rajesh Chopade1 · Accepted solution]

Hi @Sunny14 

Try bello script once 

var rec = new GlideAggregate('task');
rec.addNotNullQuery('assignment_group');  // Ensure the task has an assignment group
rec.groupBy('assigned_to');               // Group by the assigned user
rec.groupBy('assignment_group');          // Group by the assignment group
rec.addAggregate('COUNT');                // Get the ticket count for each user-group pair
rec.query();

var userGroupMap = {};

while (rec.next()) {
    var userID = rec.getValue('assigned_to');
    var userName = rec.getDisplayValue('assigned_to');
    var groupName = rec.getDisplayValue('assignment_group');
    var count = rec.getAggregate('COUNT');

    // Store the assignment groups per user
    if (!userGroupMap[userID]) {
        userGroupMap[userID] = {
            name: userName,
            groups: []
        };
    }
    userGroupMap[userID].groups.push({
        name: groupName,
        count: count
    });
}

// Print out the results
for (var user in userGroupMap) {
    var userInfo = userGroupMap[user];
    var groups = userInfo.groups.map(function(group) {
        return group.name + " (Tasks: " + group.count + ")";
    }).join(', ');

    gs.print("Assigned To: " + userInfo.name + " | Groups: " + groups);
}

[Sunny14]

Really awesome. Thank you very much @Rajesh Chopade1