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
Andrew Aldis 6Andrew Aldis 6 

Combining SOQL Queries

I am having trouble combining the following 2 sets of SOQL Queries.  I cannot do a join because they are pulling from the same object.  Basically I need to combine
Select Owner.Name, Owner.ID, Calendar_Month(CloseDate), SUM(Amount)
From Opportunity
Where CloseDate=This_Year AND StageName='Closed/Won'
GROUP BY ROllup(Owner.Name, Owner.Id, Calendar_Month(CloseDate))
With
Select Owner.Name, Owner.ID, Owner.SmallPhotoUrl
From Opportunity
Where CloseDate=This_Year AND StageName='Closed/Won'

I also need to combine
Select COUNT(CaseNumber), Calendar_Month(CreatedDate)
From Case
Where CreatedDate=This_Year
GROUP BY Calendar_Month(CreatedDate)
with
Select COUNT(CaseNumber), Calendar_Month(ClosedDate)
From Case
Where         ClosedDate=This_Year
GROUP BY Calendar_Month(ClosedDate)
logontokartiklogontokartik
Hi Andrew,
From the 1st part, I can see you have 2 selects which are returning two different types of objects.
i.e 
Select Owner.Name, Owner.ID, Calendar_Month(CloseDate), SUM(Amount)
From Opportunity
Where CloseDate=This_Year AND StageName='Closed/Won'
GROUP BY ROllup(Owner.Name, Owner.Id, Calendar_Month(CloseDate))
will return List<AggregateResult> whereas
this 
Select Owner.Name, Owner.ID, Owner.SmallPhotoUrl
From Opportunity
Where CloseDate=This_Year AND StageName='Closed/Won'
will return List<Opportunity>

For your 2nd Join, you can execute 2 queries separately and then combine them into one List and do your logic the new combined List

i.e
  
List<AggregateResult> aggrList1 = [Select COUNT(CaseNumber), Calendar_Month(CreatedDate) From Case Where CreatedDate=This_Year GROUP BY Calendar_Month(CreatedDate)]

List<AggregateResult> aggrList2 = [Select COUNT(CaseNumber), Calendar_Month(ClosedDate)From Case Where ClosedDate=This_Year GROUP BY Calendar_Month(ClosedDate)]

List<AggregateResult> aggrCombined = new List<AggregateResult>();
aggrCombined.addAll(aggrList1);
aggrCombined.addAll(aggrList2);

Hope this helps.




 
Amit Chaudhary 8Amit Chaudhary 8
It look like some places you are using Group by and some places you are not using Group by. You can not merge all query. Please check all below post. I hope that will help you.

Understanding Foreign Key and Parent-Child Relationship SOQL Queries
1) https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_foreign_key.htm

Basic SOQL Relationship Queries
2) https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.html

A Deeper look at SOQL and Relationship Queries on Force.com
3) https://developer.salesforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

Using Relationship Queries
4) https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm