I am using an aggregate SOQL query with more than one 'group by'

I'm writing a class that fires on insert/update of a trigger on the OpportunityLineItem. This looks at a picklist field (Play__c) on Product2.

When Opportunity products are added (which may be in batches) I need to count the number of each possible variation in the picklist for each OpportunityProduct.

Opp 1 has 5 products added. 2 are Red, 2 are Black and one is White. There are a number of fields on the Opp: isRed, isBlack and IsWhite into which those number must go.

My first take is that an aggregate function would do. My Primary key is Oppid and Play_c in order to count Play_c:

AggregateResult[] OppLineItemsPlays = [select OpportunityId, COUNT(id), PricebookEntry.Product2.Play__c
           from OpportunityLineItem
           where OpportunityId in: oppid
           group by OpportunityId, PricebookEntry.Product2.Play__c];
  System.debug('\n ------------------->OppLineItemsPlays = ' + OppLineItemsPlays);

for(AggregateResult OLIPlays: OppLineItemsPlays) {
   opportunityCount.put((ID) OLIPlays.get('OpportunityId'), (Integer)OLIPlays.get('expr0'));
   System.debug('\n ------------------->Opportunity id = ' + OLIPlays.get('OpportunityId'));
   System.debug('\n ------------------->Play__c = ' + OLIPlays.get('Play__c'));
   System.debug('\n ------------------->count = ' + OLIPlays.get('expr0'));
   System.debug('\n ------------------->OLIPlays = ' + OLIPlays);
//  }

This gives me a count of all of the times that each Play has been selected in Products in OLIs and gives me the name of the play.

Here is my issue and its a bit silly: how would you proceed from here? My thoughts are to create a new map for every variation of Play and then add the Oppid, Count(Play__c). This means that I have to know every possible play (which I do) and can control the creation of new ones (which I can) Alternatively a map of ids to a map of strings to integers: [map> opptyPlayCount = new map>();]

But - the items in the aggregate are objects not strings or IDs...

if( !opptyPlayCount.containsKey( OLIPlays.get('OpportunityId') ) ) 
    opptyPlayCount.put( OLIPlays.get('OpportunityId'), new Map< string, integer >( ));
if( !opptyPlayCount.get( OLIPlays.get('OpportunityId') ).containsKey( OLIPlays.get('Play__c') )) 
    opptyPlayCount.get( OLIPlays.get('OpportunityId') ).put( OLIPlays.get('Play__c'), integer( ));
opptyPlayCount.get( OLIPlays.get('OpportunityId') ).get( OLIPlays.get('Play__c') ).add( OLIPlays.get('expr0'));

So the above doesn't work and I can do this:

integer CountofType = integer.ValueOF(OLIPLays.get('expr0'));
string NameofType = string.valueOf(OLIPlays.get('Play__c'));

but not for the ID

So when I get to saving this I have the Oppids.

for(Id opptyId : summedOpptyIds) {
         opps.add(new Opportunity(
          Id = opptyId,
          Booking_Amount__c = totalBookingAmts.get(opptyId)
          isRed = ................. but how do I get this?
  • why can't you do: Id oppId = id.valueOf(OLIPlays.get('OpportunityId')); Mar 21, 2014 at 9:33
  • It doesn't work. Error message is [Save Error: Variable does not exist: id] Id opptyId = id.valueOf(OLIPlays.get('OpportunityId'));
    – SeanGorman
    Mar 21, 2014 at 13:16
  • oops now I remember. You have to cast it via String. So Id oppId = Id.valueOf(String.valueOf(OLIPlays.get('OpportunityId'))); Mar 21, 2014 at 13:24

1 Answer 1


Build the map like this:

id oppId = Id.valueOf(String.valueOf(OLIPlays.get('OpportunityId')));
integer CountofType = integer.ValueOF(OLIPLays.get('expr0'));
string NameofType = string.valueOf(OLIPlays.get('Play__c'));

if( !opptyPlayCount.containsKey( oppId  ) ) 
    opptyPlayCount.put( oppId , new Map< string, integer >( ));
if( !opptyPlayCount.get( oppId  ).containsKey( NameofType )) 
    opptyPlayCount.get( oppId  ).put( NameofType, integer( ));
opptyPlayCount.get( oppId  ).get( NameofType ).add( CountofType );

Then retrieve the value like this:

      isRed = opptyPlayCount.get(opptyId).get('Red');
  • Thanks Guy. This was awesome. I had nearly everything just the id value from the string value of the ... Then the rest just flows. Awesome help...
    – SeanGorman
    Mar 21, 2014 at 15:06
  • yes you were very close indeed :-)) Mar 21, 2014 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.