Feb 18, 2013

Fun With SOQL: Cubed Results

GROUP BY CUBE is an awesome feature of SOQL that nobody talks about. It is a very handy tool when you need to create complex visualforce reports because you can get aggregate results for different grouping of values in a single SOQL call.

Let's say that you want to create a graphical summary of the Opportunity object, and need to get the count and total of the Opportunity amounts of every combination of Stage, Type, and Lead Source. Well, you're in luck, because with Just one "cubed" SOQL call you can get enough data to fill a visualforce page with interesting charts:

The above statement returns:
  • The count and subtotal for every combination of Stage, Type, and Lead Source (That's as many rows as the number of Stage picklist values times the number of Type picklist values times the number of Lead Source picklist values).
  • The count and subtotal for each two field combination where the values of the third field does not come into consideration (all two field combinations).
  • The count and subtotal for each field value regardless of the value in the other two fields (for each of the three fields).
  • The count and total for all the values.

Here is how this works:
  • The GROUP BY CUBE section determines which field values get aggregated.
  • The GROUPING() in the SELECT section provides a way for you to evaluate what subtotal each row corresponds to - a zero in that function means that the row is grouped by the specified field, and a one means that it doesn't (seems counter intuitive, but that's how it works). See example of that below.
  • The aggregate results in the SELECT provide the data. A nice addition here could be using Account for one of the grouped results and use COUNT_DISTINCT(Account) to get count of unique accounts as opposed to unique opportunities.
  • The ORDER BY section is very important because it helps you process the data. By sorting the GROUPING() functions first, the results come up in a predictable order that you can take advantage of in an Apex loop.

Here is an example of how you can harvest the cubed results:

You can find GROUP BY CUBE in the documentation here.