Change format fields in SOQL [closed]

Question

I do the next SOQL and works:

SELECT FieldString FROM Account GROUP BY FieldString HAVING COUNT(FieldString)>1

But when I do the next SOQL doesn't work:

SELECT FieldDouble FROM Account GROUP BY FieldDouble HAVING COUNT(FieldDouble)>1  

Is it possible to make a format change for the SOQL to work? Or is there an alternative to making this SOQL work?

Answer 1

What makes a field not groupable

This was somehow answered here.

However, to your case, custom number fields cannot be grouped by, same as text formulas with value TEXT(FieldDouble).

The only solution if you do not want to modify your SOQL is to create a text field that will be updated from a workflow anytime the FieldDouble changes with its value.

Answer 2

This query is not going to work because you are not doing any aggregation.

HAVING clause only works on aggregations. Available aggregations in SOQL are: AVG(), COUNT(), COUNT_DISTINCT(), MIN(), MAX(), SUM().

If you want to perform your SOQL query, you should aggregate your FieldDouble field and group by groupable fields.

Working example:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

Documentation:

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_having.htm