How to use Count() in SOQL

by

I have a custom object. This custom object has a field Cabin_Type__c,
which has values of Twin and Luxury. There is another field
Deck_Type__c which is just the number of the deck such as 1,2,3,etc.

So here are what a few of my custom objects might look like

obj1={Cabin_Type__c=Twin, Deck_Type__c=1} 
obj2={Cabin_Type__c=Twin, Deck_Type__c=1} 
obj3={Cabin_Type__c=Twin, Deck_Type__c=1}
obj4={Cabin_Type__c=Luxury, Deck_Type__c=1} 
obj5={Cabin_Type__c=Luxury, Deck_Type__c=1}
obj6={Cabin_Type__c=Twin, Deck_Type__c=2}
obj7={Cabin_Type__c=Twin, Deck_Type__c=2}

From the above data, I would like to be able to extract through SOQL the
count of cabin types by deck. So from the 7 objects above, I would get

Deck 1 Twin (3)

Deck 1 Luxury (2)

Deck 2 Twin (2)

Is there something in SOQL similar to count() in SQL that I could use for this?

Answer

You would want to use an AggregateResult query here. This is obviously just some shorthand code as I really don’t know your object or field names so you will have to adjust those to fit in order for this to compile for you

AggregateResult[] ARs
  = [SELECT count(Id) myCount, Cabin_Type__c, Deck_Type__c
      FROM cruise_ deck_Cabin_paln__c
      GROUP BY Cabin_Type__c, Deck_Type__c];

This will return a a list of AggregateResults objects. Each aggregate result object retruned by this query will look something like this

{myCount=2, Cabin_Type__c=Twin, Deck_Type__c=2}

Depending on how you plan to use the results would affect how I would suggest manipulating them. I often create a wrapperclass. If you wanted to use a wrapper class here it would have 3 properties, Deck, Cabin Type, and Quantity. It would look something like this

public class myWrapper {
    public Integer Quantity       { get; private set; }
    public String Deck            { get; private set; }
    public String CabinType       { get; private set; }

    public myWrapper(AggregateResult ar) {
        Quantity = (Integer) ar.get('Quantity');
        Deck = (String) ar.get('Deck_Type__c');
        cabinType = (String) ar.get('Cabin_Type__c');
    }
}

And you could populate it like so

    List<myWrapper> wraps = new List<myWrapper>();
    for (AggregateResult ar : ARs) {
        wraps.add(new myWrapper(ar));
    }

But for purposes of just being able to easily access the data, you could just populate a map with the results like so

map<string,integer> countMap = new map<string,integer>();

    for(AggregateResult ar : ARs){
        countMap.put((string) ar.get('Cabin_Type__c') + ',' + (string) ar.get('Deck_Type__c'), (integer) ar.get('myCount'));
    }

You now have a map where the key is the concatenation of the Deck and Cabin type, and the value it returns is the count.

Attribution
Source : Link , Question Author : Reddy , Answer Author : Chris Duncombe

Leave a Comment