How to use SOQL Count()?

448 Asked by diashrinidhi in Salesforce , Asked on May 12, 2023

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?

Answered by Darsh K

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 list of AggregateResults objects. Each aggregate result object returned 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 wrapper class. 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 wraps = new List(); 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 countMap = new map(); 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 soql count.

How to use SOQL Count()?

Your Answer