SOQL query calculation and displaying the results of query

Question

The following query works fine. What I need from it is...

1. To get the total count of the ORG__c field and then for each value, what is the percentage of the total. I.e. if there are 50 records with ORG__c, 20 have a value of 'a', 14 have a value of 'b' and 16 have a value of 'c', I need the percentage of each value vs. the total.

2. How to display the fields in the query in a HTML table on a VF page grouped by BOG_Membership_Type_New__c then by ORG__c?

3. How to display #1 on the bottom of the same VF page.

Here is the query...

Select BOG_Membership_Type_New__c, ORG__c, contact__r.Name 
FROM BOG_Membership__c 
WHERE BOG_Membership_Status__c = 'Active' 
AND BOG_Membership_Type_New__c <> '' 
ORDER BY BOG_Membership_Type_New__c 

Answer 1

A few quick notes before the sample code:

I'm still fairly new to VF myself, so I'm not going to attempt to help you with that portion of it. What I can say is that from my understanding, you can't do everything you're trying to do in that query, nor can you do it in the VF page itself. You'll need to do it in a controller or a controller extension (depending on your situation and what else you're trying to accomplish in this project) that the VF page uses.

Lastly, when you say "..total count of Org__c" are you referring to the total number of BOG_Membership__c records, since unless you're using Record Types that have different fieldsets, all of your records will have that field, or are you referring to BOG_Membership__c records that have a value versus not having a value at all? For the time being, I'm going to assume you mean all BOG_Membership__c records, and use that list size.

Also, I don't know what type of field BOG_Membership_Type_New__c is, but it sounds like a Boolean (checkbox), so I'm going to treat it as such. The same goes for Org__c which sounds like an Id even though your examples show a string.

The code:

List<BOG_Membership__c> bogQueryList = *your query here*;

Map<Boolean, List<BOG_Membership__c>> membershipByStatus = new Map<Boolean, List<BOG_Membership__c>>();
Map<Id, List<BOG_Membership__c>> membershipByOrg = new Map<Id, List<BOG_Membership__c>>{'a' => new List<BOG_Membership__c>{}, 'b' => new List<BOG_Membership__c>{}, 'c' => List<Bog_Membership__c>{}};

Decimal a = 0.00;
Decimal b = 0.00;
Decimal c = 0.00;

for(BOG_Membership__c record : bogQueryList){
   if(record.Org__c == 'a'){membershipByOrg.get('a').add(record);}
   else if(record.Org__c == 'b'){membershipByOrg.get('b').add(record);}
   else if(record.Org__c == 'c'){membershipByOrg.get('c').add(record);}

   if(membershipByStatus.containsKey(BOG_Membership_Type_New__c)){membershipByStatus.get(BOG_Membership_Type_New__c).add(record);}
   else{membershipByStatus.put(BOG_Membership_Type_New__c, new List<BOG_Membership__c>{record};} 
}

if(bogQueryList.size() > 0){
   a = membershipByOrg.get('a').Values().size() / BOG_Membership__c.size();
   b = membershipByOrg.get('b').Values().size()/ BOG_Membership__c.size();
   c = membershipByOrg.get('c').Values().size()/ BOG_Membership__c.size();
}

How you'll want to list the variables with additional getter/setter methods is a different story. You'll want to familiarize yourself with that terminology/their usage to know what's appropriate for your use case.

Regardless, by putting the BOG_Membership__c objects in a map grouped the way you want them, you can use a repeat tag to build a table from the values that accurately reflects that grouping.

More on using Maps in VF:

https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_dynamic_vf_maps_lists.htm

EDIT:

I didn't clarify this originally, since I wasn't sure of how you'd want to present a "percent" to the user, but if you wanted to display the value as text formatted like a percent (i.e. "20%" versus "0.20"), straight from variable to page using something like outputText, you'd want to make this slight adjustment:

String a = '0%';
String b = '0%';
String c = '0%';

if(bogQueryList.size() > 0){
   a = String.ValueOf((membershipByOrg.get('a').Values().size() / BOG_Membership__c.size()) * 100) + '%';
   b = String.ValueOf((membershipByOrg.get('b').Values().size() / BOG_Membership__c.size()) * 100) + '%';
   c = String.ValueOf((membershipByOrg.get('c').Values().size() / BOG_Membership__c.size()) * 100) + '%';
}