GlideAggregate SUM not summing correction? Is it summing by groups of your sum field?

[garyopela]

Hey all, I just wanted to share some tidbits here....

So I was just running a GlideAggregate SUM against sys_attachment in my test instance. The results were, well, weird. Instead of one result that was the sum of all of the size_bytes for all of the records that matched my query, I was getting multiple results back.

Upon further investigation, I found out that it was actually grouping records by their size_bytes, then summing the groups.

For example, if I had the following rows:

Row 1: size_bytes: 100

Row 2: size_bytes: 200

Row 3: size_bytes: 200

Row 4: size_bytes: 200

Row 5: size_bytes: 300

The results of a GlideAggregate SUM size_bytes would be:

100

600

300

Instead of:

1000

So, after talking to some co-workers, one pointed me to a GitHub page, and on it, they were using setGroup(false) in the GlideAggregate query. I tried that, and it worked. So, below are the scripts:

First, the non-working script:

var grA = new GlideAggregate('sys_attachment');
grA.addAggregate('SUM', 'size_bytes');
grA.addQuery('table_name', 'sys_certificate');
grA.query();
while (grA.next()){
    gs.print('SUM: ' + grA.getAggregate('SUM', 'size_bytes'));
}

The results of this script are:

*** Script: SUM: 449
*** Script: SUM: 1716
*** Script: SUM: 5154
*** Script: SUM: 1483
*** Script: SUM: 1532
*** Script: SUM: 2286
*** Script: SUM: 2794

Now the working script:

var grA = new GlideAggregate('sys_attachment');
grA.addAggregate('SUM', 'size_bytes');
grA.setGroup(false);
grA.addQuery('table_name', 'sys_certificate');
grA.query();
while (grA.next()){
    gs.print('SUM: ' + grA.getAggregate('SUM', 'size_bytes'));
}

And the results:

*** Script: SUM: 15414

Coincidentally, if you add a grA.orderBy('size_bytes'); then it will cause it to start grouping again. I found that out the hard way!

Anyways, I hope this quick blurb helps someone out. Let me know if you've run into this before, too, or if this post helped you!