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.

Attribution to: Guilherme Gobetti

Possible Suggestion/Solution #1

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

Attribution to: Eric Leschinski

Possible Suggestion/Solution #2

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

Attribution to: Jon Hazan

Possible Suggestion/Solution #3

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

Attribution to: Mohith Shrivastava
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1828