0

How do I create a SOQL GROUP BY using custom field?

For example:

SELECT id, name, Cultura__c 
FROM SC_VPM__c 
GROUP BY Cultura__c

This returns an error:

MALFORMED_QUERY.
  • 1
    In that particular GROUP BY clause, SC_VPM_c and Cultura_c should be SC_VPM__c and Cultura__c (two underscores). Was that the problem? – metadaddy Oct 1 '12 at 15:13
  • 2
    In addition to the lack of underscore, you can only select the Culture__c field in addition to aggregate functions such as count, min, max, etc – James Loghry Oct 1 '12 at 15:43
  • 2
    What did you try? What errors did you receive? – jkraybill Oct 2 '12 at 6:39
3

You should be able to group with a custom field just like a standard one. The main difference is the result from a group by query is that it becomes an AggregateResult sObject.

Salesforce Documentation of SOQL GROUP BY: http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/langCon_apex_SOQL_agg_fns.htm

| improve this answer | |
2

This is exclusively for SOSL AND SOQL. Defined at this link:

http://www.salesforce.com/us/developer/docs/soql_sosl/index_Left.htm#StartTopic=Content/sforce_api_calls_soql.htm

You can use a GROUP BY clause without an aggregated function to query all the distinct values, including null, for an object. The following query returns the distinct set of values stored in the LeadSource field.

SELECT LeadSource
FROM Lead
GROUP BY LeadSource
| improve this answer | |
0

Salesforce group by example

The gotcha is that in SOQL, you can't use a bare count() in a group by query.

As a work around, you can specify a field to go inside the count like this:

select mycolumn, count(id)
from Opportunity
group by mycolumn

This produces results for me:

mycolumn              Unknown_Field__1
skipper               5
kowalski              3
rico                  1

A more advanced query using group by:

This gets the unique values of insurancecarrier__c and how many there are in the Object called Opportunity. It sorts descending by the 2nd column, and limits to 100 results.

select insurancecarrier__c, count(id)
from Opportunity
group by insurancecarrier__c
order by count(id) desc
limit 100
| 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.