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? – Shebin Mathew Dec 3 '13 at 9:50
  • 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? – zdropic Dec 3 '13 at 9:55
  • 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 – Reddy Dec 3 '13 at 10:06

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.

|improve this answer|||||
  • Is it possible to not count not filled fields - that with null? – Vitaly Zdanevich Jun 1 '17 at 6:44

if you're objects are related to one another such that Deck (obj) has Cabins(obj) you can accomplish this without code using role up summary fields and their filters. Count(cabins) where name = twin for instance.

also, note that Database has a static method called countQuery that returns an integer and can be used thusly:

Integer TwinCabinsDeckOne = Database.countQuery('SELECT Count(id) from .... where deck = 1 and cabintype = twin'); 

but I suspect what you're really looking for is a nice aggregate query:

something like this:

AggregateResult[] CabinTypesOnDecks = SELECT Deck, CabinType, COUNT(id) cnt
FROM cabins__c
GROUP BY ROLLUP(Deck, CabinType);

that would let you get all the counts for all decks for all cabin types in one query. Now processing that queries' results is up to you!

|improve this answer|||||

It is simple if we create a formula field to hold the three fields and apply group by on that field.

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