Trying to make the mental leap from SQL to SOQL is proving a bit challenging for me and some of the online documentation isn't making it clear where I am going wrong. Here is my current query, which is generating an unknown parsing error:
SELECT Account.Name, Account.Type, Account.VendorLocations__c, Account.Annual_New_Locations__c, Account.BusDev_Manager__r.Name, Account.Relationship_Manager__r.Name, (select sum(Total_MIDs__c) from Opportunity where Development_Partner__c = Account.Account_ID__c) FROM Account WHERE Account.Type IN ('Partner - Dealer','Partner - ISV','Partner - Reseller') ORDER BY Account.Name ASC
I am trying to pull a list of all my Accounts that are partners and for each partner get a total of the size of opportunities that are related to them. However the opportunities are not under the partner Account, they are under the individual client Accounts, with a custom reference field to the Partner.
Where am I going wrong, or how better can I achieve this. I am using this with Conga Composer.