Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
harshasfdcharshasfdc 

Group by function

Hi All,

 

I am trying to use group by function in soql query without using aggregate function  it is showing error is it possible to use group by function without  aggregate the values??

 

 

Thanks

Harsha

@anilbathula@@anilbathula@

Hi

 

check this link for using group by without using aggregate function

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_groupby.htm

 
OyeCodeOyeCode

No you have to use aggregate methods everytime with group by clause

 

Try running these two queries in your Developer console SOQL Query editor

 

This will be success 

 

SELECT Id, Avg(Amount)
FROM Opportunity
Group by Id
limit 10

 

While if you take group by - you are groping Id here but not Amount - how can we represent the data with this query 

 

This will fail 

 

SELECT Id, Amount
FROM Opportunity
Group by Id
limit 10

 

SFDC query returned an error: 

{faultcode:'sf:MALFORMED_QUERY', faultstring:'MALFORMED_QUERY: Field must be grouped or aggregated: Amount', detail:{MalformedQueryFault:{exceptionCode:'MALFORMED_QUERY', exceptionMessage:'Field must be grouped or aggregated: Amount', row:'-1', column:'-1', }, }, }
sfdcfoxsfdcfox

As ForceLabs pointed out, you must aggregate or group. So, the best you can do to "not aggregate" is:

 

select id, amount
from opportunity
group by id, amount
limit 10

But, if there are duplicate amounts, they will be grouped together, so you won't know how many there were without using at least Count(Amount).