function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

How to group by number field?

Hi All

 

I need to query the distinct value for a field, which is like "select xxx(number field) from yyy group by xxx(number field)." But system threw error as "field 'xxx' can not be grouped in a query call". 

 

Is there any way to implement such distinct query for a number field?

 

Thanks

Kunlun

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

AggregateResult[] groupedResults= [SELECT xxx, count(Id)  FROM  yyyy  GROUP BY Ax__c];
for (AggregateResult ar : groupedResults) {
System.debug(' ID>>>>>>>>>>>>>>' + Integer.valueOf(ar.get('xxx')));
}

 

or

set<Integer> s = new set<Integer>();

for(xxx  a : [select xxx from yyyy where some condition]){

      s.add(a.xxx);   //this set will hold only unique numbers

}
KunlunKunlun

Thanks for your reply.

 

1#, I can not understand it clearly for the grouped field "Ax__c".

2#, yes, it is a good way, but there maybe thousands of records so it is better to query distinct with SOQL instead of apex. Is there other methods to implement it?

 

My records likes below,

ID, xxx

1, 20

2, 3

3, 20

4, 10

5, 10

6, 5

7, 3

8, 5

9, 20

 

I needs to query xxx fields like below:

3

5

10

20

 

 
kranjankranjan

Hi Kunlun,

 

You can alternatively try creating a new text field say zzz and can populate the same with the xxx field value in INSERT, UPDATE trigger for yy object. And then use this zzz field in group by clause. so the query could be something like this:

 

SELECT zzz from yy GROUP BY zzz

 

Hope that helps.
SamuelDeRyckeSamuelDeRycke

No, I think iterating your records and adding them to a set is the only option for numbers. 

 

 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

 

List<Integer> nums = new List<nums>();

 

AggregateResult[] groupedResults= [SELECT xxx, count(Id)  FROM  yyyy  GROUP BY xxx];
for (AggregateResult ar : groupedResults) {


System.debug(' ID>>>>>>>>>>>>>>' + Integer.valueOf(ar.get('xxx')));  

nums.add(Integer.valueOf(ar.get('xxx')));
}
SamuelDeRyckeSamuelDeRycke

Sam, did you actually try that ?  The documentation states nothing about it, but trying to group by a number field does give a malformed query exception.

 

SELECT numberfield__c, count(Id)  FROM  testobject__c  GROUP BY numberfield__c

 this results to : 

 

MALFORMED_QUERY: 
FROM testobject__c GROUP BY numberfield__c
^
ERROR at Row:1:Column:65
field 'numberfield__c' can not be grouped in a query call

 

 

 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

I tried on text field. It worked.

As you said, for Numeric field its not working... I tried creating a formula field of type text from the existing number field but it didn't worked for grouping.  

 

So I believe only way is to use set to collect unique numbers list... or create another text field and create a Workflow rule to update the newly created text field to TEXT(Numberfield) so that you can group on this field instead of number field. Not sure why it is not supporting ... couldn't find any in documentation :(

 
Rahul Joshi 2Rahul Joshi 2
1. Create a text field with same capacity as the number field i.e. numberfield_Text__c Text(11). My numberfield__c was Number(11,0).
2. Run below code, which will copy data from numberfield__c to numberfield_Text__c.

testobject__c[] DBUpdate;
DBUpdate = [SELECT id, numberfield__c, numberfield_Text__c FROM  testobject__c];

for (testobject__c a : DBUpdate) {
  a.numberfield_Text__c = string.valueOf(a.numberfield__c);
}

try {
    update DBUpdate;
} catch(DmlException e) {
    System.debug('An unexpected error has occurred: ' + e.getMessage());
}

3. Run below SOQL:
SELECT   numberfield_Text__c, count(id) 
FROM     testobject__c 
group by numberfield_Text__c
having count(id) > 1