Oct 10, 2013

Anatomy of the Group By Cube SOQL Query

Ahead of my Dreamforce session Custom Analytics Using SOQL Cubed Results (register here), I decided to write a preview post about the Group By Cube syntax you want to use.

Let's start with a simple SOQL statement that retrieves three fields from the Case object, and filter it to only bring back records that are closed. I always add aliases to the fields in Group By queries because it makes working with the aggregated results in Apex a little easier, and eliminates the need to include the namespace in Apex if you're packaging the code for the. AppExchange.

Next, add the GROUP BY CUBE Statement with all 3 fields that are being retrieved. The order of the fields here does not make a difference as groupings for all field combinations are returned in the query.

Next add some aggregate functions. Getting summarized information is why you use Group By Cube, so its you chance to go nuts with AVE(), MIN(), SUM(), COUNT(), etc. Don't forget to ad an alias for the function here as well.

Because cubed results return 8 types of groupings, we need a way to identify which fields are used for the subtotals of each row. For that we can use the SOQL Grouping() function, which is basically like asking the platform if the field values are included in the specific row.

The last thing that we want to add is a way to assure that the results are sorted in a predictable way. This will make processing in Apex more convenient and economical. For that we are going to add an Order By clause that will assure that each subtotal grouping will be presented together. We also want to sort the values within each grouping, so you should add the 3 fields from the select to the end of the Order By.

That's it! Here's how the query looks like:

This is what the query returns with some sample results: