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
TempleHoffTempleHoff 

GROUP BY with nested query

I need to display a list of accounts grouped by a common field (Ulitmate_Parent_Id).  Then as a subquery list all the opportunities on each account.

 

Heres my SOQL:  

List<Account> accounts = [Select Id, Account_Family__c, Name, BillingCity,(Select Name, StageName, Owner.Name, From Opportunities) from Account where GROUP BY Account_Family__c];

 

Problem is I get an error saying that GROUP BY is not allowed in a query with a subquery.  But I need it GROUPed BY.  So how do I get arround this?  Can I execute the query then somehow generate the grouping on the list afterwords?  

GunnarGunnar

I had a similar issue. The group by is an aggregate. There are limitations.

To get around, I wrote the result of the aggregate to another table(object),

Then did further work with the info being written to this other table.

 

It appears that the aggregate result is an array, not a 'list'.

Too bad you can't do SELECTs on existing lists! I have not seen this ability.

Too bad you can't declare temporary tables.

 

Be careful, you are limited on the number of records going into an aggregate and on the number of records output by an aggregate. Great place to be breaking down your records into some type of category and submitting to batch operations.

But again, be careful, as you are limited to creating -5- batches in code.