Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mike @ BlackTabMike @ BlackTab 

SOQL GROUP BY Issue

I'm having some trouble returning data from this SOQL query:

 

SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CreatedDate)

 It returns absolutly nothing even though I have multiple opportunities with amounts.

 

Here is the full method:

 

@RemoteAction
    global Static List<sObject> loadOppsByMonth(){
    	return [SELECT CALENDAR_MONTH(CreatedDate), SUM(Amount), SUM(ExpectedRevenue) FROM Opportunity GROUP BY CALENDAR_MONTH(CreatedDate)];
    }

 Is my return datatype wrong? Should I be looping through the data returned from this query?

 

Full context of the situation: I'm trying to write a query that provides summerized data to a LineChart using the Google Charting API

Best Answer chosen by Admin (Salesforce Developers) 
mjohnson-TICmjohnson-TIC

Not sure of the need to make this global or @remoteaction, I have used google chart api in visualforce with list data returned from apex classes without issue. In any case, try using the list returned from this.

 

public list<AggregateResult> getOppsByMonth(){
AggregateResult[] a = [Select CALENDAR_MONTH(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_MONTH(CreatedDate)];
return a;
}

 The problem is you are working with an aggregateresult, not an sobject. If you are not comfortable working with aggregateresults, you can always put them into a wrapper class to iterate through in visualforce.

 

public list<wrapperopp> getOppsByMonth(){
list<wrapperopp> ol = new list<wrapperopp>();
for(AggregateResult a: [Select CALENDAR_MONTH(CreatedDate)month, SUM(Amount)amount FROM Opportunity GROUP BY CALENDAR_MONTH(CreatedDate)]{
wrapperopp o = new wrapperopp();
o.month = decimal.valueof(string.valueof(a.get('month')));
o.amount = decimal.valueof(string.valueof(a.get('amount)));
ol.add(o);
}
return ol;
} 

public class wrapperopp{
public decimal month{get;set;}
public decimal amount{get;set;}
}

 Hope this helps.

All Answers

mjohnson-TICmjohnson-TIC

Not sure of the need to make this global or @remoteaction, I have used google chart api in visualforce with list data returned from apex classes without issue. In any case, try using the list returned from this.

 

public list<AggregateResult> getOppsByMonth(){
AggregateResult[] a = [Select CALENDAR_MONTH(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_MONTH(CreatedDate)];
return a;
}

 The problem is you are working with an aggregateresult, not an sobject. If you are not comfortable working with aggregateresults, you can always put them into a wrapper class to iterate through in visualforce.

 

public list<wrapperopp> getOppsByMonth(){
list<wrapperopp> ol = new list<wrapperopp>();
for(AggregateResult a: [Select CALENDAR_MONTH(CreatedDate)month, SUM(Amount)amount FROM Opportunity GROUP BY CALENDAR_MONTH(CreatedDate)]{
wrapperopp o = new wrapperopp();
o.month = decimal.valueof(string.valueof(a.get('month')));
o.amount = decimal.valueof(string.valueof(a.get('amount)));
ol.add(o);
}
return ol;
} 

public class wrapperopp{
public decimal month{get;set;}
public decimal amount{get;set;}
}

 Hope this helps.

This was selected as the best answer
Mike @ BlackTabMike @ BlackTab
Yeah, this should help.thanks :)