I have got a situation where there is a filed named "Sub-Category" which is dependent on its master "Category", 3 check boxes and a formula field. So, when I select a "Category" (for ex. X), then a "Sub-Category" (for ex. Y) and then check a check-box. Doing so, a formula field will say as "Available Count: 1" , because, I have checked a single check box. Similarly, if I create another record with Category as X, Sub-Category as Z and check 2 check boxes, then the "Available Count will be 2".

Now, I am supposed to display the "Available Count" in the VF page along side its "Sub Category" grouped under their respective "Category".

For which, I have written the following query

AggregateResult[] groupedResults = [SELECT Category__c, SUM(Available_Spots__c)totalAvailable
                                    FROM FAQ_Advertising_Inventory__c
                                    GROUP BY Category__c

    for (AggregateResult ar : groupedResults)  
        System.debug('Category List' + ar.get('Category__c'));                        
        System.debug('Total Available' + ar.get('totalAvailable'));

I had written this query in the Constructor.

And I am getting the right values (Category List & Total Available) when I debug the above ones. However, I also need to get the Subcategory grouped under Category, which I am unable to do so & I need to know how to display the result of the above query on VF page.


If I understand correctly you're looking for another field to get from your grouping... in order to achieve this you just group all the fields you are querying independently in order to receive this information.

SELECT Category__c, SubCategory__c, SUM(Available_Spots__c)totalAvailable FROM FAQ_Advertising_Inventory__c GROUP BY Category__c, SubCategory__c

To display this information within Salesforce lets say you return groupedResults you'll access the values when looping through it with something like this:

<apex:repeat value="{!groupedResults}" var="gr"> {!gr['Category__c']} has a count of {!gr['totalAvailable']} </apex:repeat>

Lastly if you are looking at getting records with a a sum of at least 1, use the HAVING clause as shown here Salesforce Doc for HAVING

SELECT Category__c, SUM(Available_Spots__c)totalAvailable FROM FAQ_Advertising_Inventory__c GROUP BY Category__c HAVING SUM(Available_Spots__c) > 1

| improve this answer | |

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.