+ Start a Discussion

dynamic SOQL With group by clause(very urgent)



  i wanna a use group by clause in my Soql but we need to do in dynamically. is it possible



i am getting fields and object and condition are dynamically.


now i need to add group by in that query which is selected by the user.


 is it possible with dynamic soql.




Ashok chandra


yes, its possible, cosider the following example


String condQueryO='SELECT SUM(Amount) FROM Opportunity WHERE Probability >0';
        condQueryO=condQueryO+' Group By Type';
    condQueryO=condQueryO+' Group By Account';
List<AggregateResult> amtList=new List<AggregateResult>();
system.debug('@@@ condQueryO '+condQueryO);
     amtList = Database.query(condQueryO);        
}catch(Exception e){

 Hope it helps.





Yes you can do this. Try the below code as reference:
string groupBy='Account.Name';
String con='select Account.Name AccountName,Count(id) from contact group by ' +groupBy;
system.debug('#############' +con);
list<contact> co = database.query(con);


These two senarios are happenig when we knew the fileds and we can do group by with those fields.


my senario is 


 i am displaying all the objects in picklist, when we select one object from picklist i am dispalying all the fields respect to selected object.

  Then will select the fields from that picklist as a fields for the soql query...................


Now we need to group by the some other fields in that object






you can easily do that with the same code, just change the group by field to a string variable and from the selection, put the values in this variable. If there are more than 1 field to group by, you can make this string a comma separated one.


String groupBy='Type';
String condQueryO='SELECT SUM(Amount) FROM Opportunity WHERE Probability >0';
condQueryO=condQueryO+' Group By '+groupBy;




this is my page



<apex:page Controller="Describer">
<apex:form id="Describe">
<apex:pageBlock id="block2" >
<apex:pageblockbuttons location="top" >
<apex:commandButton value="Get Describe Object Fields" action="{!showFields}" />
<apex:pageblocksection id="text1" >
<apex:selectList value="{!selectedObject}" size="1">
<apex:selectOptions value="{!objectNames}"/>
<apex:pageblocksection id="fieldList" rendered="{!not(isnull(selectedObject))}">

<apex:selectList value="{!selectedfield}" size="1" multiselect="true">
<apex:selectOptions value="{!filedNames}"/>
<apex:commandButton value=">>" action="{!addfieldsforquery}" rerender="text3"/>

<apex:pageBlockTable value="{!selectedfield}" var="sfield" id="text3">
<apex:column value="{!sfield}"/>
<!--<apex:column >

<apex:selectList value="{!selectedfieldforgroupby}" size="1" id="text4">
<apex:selectOptions value="{!function}"/>

<apex:commandButton value="runquery" action="{!runquery}"/>
<apex:pageBlockSection >
<apex:pageBlockTable value="{!queryresult}" var="que">

<apex:column value="{!que}"/>






this is my class



public class Describer

public List<SelectOption> getFunction() {
selectoption stringcondition1=new selectoption('SUM','SUM');
selectoption stringcondition2=new selectoption('AVG','AVG');
selectoption stringcondition3=new selectoption('MIN','MIN');
selectoption stringcondition4=new selectoption('MAX','MAX');



return conditions;



public list<Sobject> queryresult{get;set;}
//public list<AggregateResult> queryresult{get;set;}
public PageReference runquery() {

string soql='select'+' '+ selectedobjectsforquery + ' from ' + selectedObject +' limit 10 ';

try {

queryresult= Database.query(soql);

} catch (Exception e) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));

return null;

public PageReference addfieldsforquery() {

if(!selectedfield.isEmpty() ){
for(string s:selectedfield)


integer sizes=selectedobjectsforquery.length();
return null;

public Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
public List<Pair> lstfieldname{get;set;}
public List <Pair> fields {get{return lstfieldname;} set{lstfieldname =value;}}
public List <SelectOption> objectNames{public get; private set;}
public String selectedObject {get; set;}
public list<String> selectedfield{get;set;}

public List<SelectOption> conditions= new List<SelectOption>();

public String selectedfieldforgroupby{get;set{selectedfieldforgroupby=value;}}
public list<String> selectedfieldsforgroupby{get;set;}
public String selectedobjectsforquery{get;set;}
public List <SelectOption> filedNames{public get; private set;}

//public list<pair1> filedwithfunction{get;set;}

// Intialize objectNames and fields
public Describer() {
objectNames = initObjNames();
fields = new List<Pair>();

//filedwithfunction=new list<pair1>();
selectedfieldsforgroupby=new list<string>();


// Populate SelectOption list -
// find all sObjects available in the organization
private List<SelectOption> initObjNames() {
List<SelectOption> objNames = new List<SelectOption>();

List<String> entities = new List<String>(schemaMap.keySet());
for(String name : entities)

objNames.add(new SelectOption(name,name));
return objNames;

// Find the fields for the selected object
public void showFields() {
filedNames=new list<SelectOption>();
system.debug('$$$$$' + selectedObject);
Map <String, Schema.SObjectField> fieldMap = schemaMap.get(selectedObject).getDescribe().fields.getMap();
for(Schema.SObjectField sfield : fieldMap.Values())
if (sfield.getDescribe().isAccessible() && !sfield.getDescribe().isDefaultedOnCreate() && sfield.getDescribe().isUpdateable()&& sfield.getDescribe().getLabel()!='Languages' && sfield.getDescribe().getLabel()!='Level' && sfield.getDescribe().getLabel()!='Account ID' && sfield.getDescribe().getLabel()!='id' && sfield.getDescribe().getLabel()!='Reports To ID' && sfield.getDescribe().getLabel()!='Email Bounced Date' && sfield.getDescribe().getLabel()!='Email Bounced Reason'){
schema.describefieldresult dfield = sfield.getDescribe();
Pair field = new Pair();
field.key = dfield.getname();
field.val = dfield.getType () + ' : ' + dfield.getLabel ();
filedNames.add(new SelectOption(field.key,field.key));

public class Pair {
public String key {get; set;}
public String val {get; set;}




here i am getting objects and fields dynamically now i want to add group by in soql......................


I don't see the part in the code where you are selecting the field to group with, do you intend to group by the selectedobjectsforquery  as well?



i want those fields for group by also. we can add 'group by selectedobjectsforquery' to soql string. then we will get aggregate results.......


one more problem what i am facing

  Here my problem is how to show the result in page.....


u may suggest wrapper class but we don't know the fields exactly because it may vary (user is selecting the fields from picklist)


This is my problem


sorry if i am asking Non-sence question


I'm a bit confised now, so you original issue of group by is now solved right? Now you have a different issue to dynamically display data of various fields that were selected? I think you should post that question under the Visualforce thread and people with better visualforce skills might be able to help you out.


Yes i got the solution......... i will post it in visual force



Thanks so much




Ashok Chandra

Hi Ashok,

Could you please post the solution? I am also facing the same scenario.

Thanks in Advance,
Indhu R