[SalesForce] AggregateResult for a multi-select picklist

I am doing an AggregateResult SOQl query on different picklists on a Contact.

List<AggregateResult> results = [select LeadSource, COUNT(id)cnt from Contact where LeadSource != null group by LeadSource];

Where LeadSource is of type Pick list. This basically gives me the number of records for each value in the LeadSource picklist.

The problem however comes when doing this same type of AggregateResult query for a multi-select picklist. It says I cannot do a group by for a multi-select picklist.

Is there any other way I can do AggregateResult for a multi-select picklist?

Best Answer

https://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_select_agg_functions_field_types.htm

As per the document link above there is no way to use AggregateResult

However if the no of records are less than 50000 rows you can still loop over the records and use Maps to seggregate and do the count

If you are building something like reporting use @ReadOnly attribute on page so that the no of Rows retrieved is increased .

Related Solutions

[SalesForce] assign Aggregate SOQL Result directly to variable

You cannot simplify the query, but you can the assignment...

Decimal avgAmount = (Decimal) [SELECT AVG(Amount)aver FROM Opportunity][0].get('aver');

Normally I would have said this was a little unsafe, since your always assuming you have one item in the result array the aggregate query returns. However I've just tested it and even if the object has no records in it you do get an a single aggregate result. However the value of 'aver' is null.

[SalesForce] Querying against a multi-select picklist

If you are querying against a mulit-select picklist, you need to use includes in your query.

list subscriptionCampaigns = [
    SELECT ID FROM Campaign 
    WHERE Newsetter_Subscriptions__c INCLUDES :contactSubscriptions];

If Newsletter_Subscriptions__c is the multi-select and contactSubscriptions contains all of the values you want this should return any Campaign that has at least one selected. If you need to only select those that match all then you need to join the contactSubscriptions into a single semi-colon delimited string and have that as the only item on the list

There are some more examples in the Querying Multi-Select Picklist docs

Best Answer

Related Solutions

[SalesForce] assign Aggregate SOQL Result directly to variable

[SalesForce] Querying against a multi-select picklist

Related Topic