How to use Count() in SOQL

  • 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?

    Can you please elaborate?

    So you want to count the number of booked cabins by type for each deck? I'd expect you just want to do "group by deckType, cabinType". Is that what you are aiming at?

    for every deck i want to count cabins with same name. Like in above records i have 3 twin in deck1, so twin count is 3, for deck1 luxury is 2 so luxury count is 2 and for deck2 twin cabins are 2 so twin count is 2

  • 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.

    Is it possible to not count not filled fields - that with `null`?

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM