GROUP BY Clause SOQL

Question

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.

Answer 1

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

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

Answer 3

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