I'm trying to summarize amounts and count opportunities in this quarter that are related to accounts with no prior opportunities closed won.

In normal SQL this would be a very easy join on the same opportunity table.

One way would be to build a list with all accounts with prior closed opportunities and use that list to filter the new opportunities. Is there another way to do this without returning that many rows to just aggregate at the end?

Just want to make sure I'm not missing something very basic.

1 Answer 1


Why do we need SOQL here at all? A couple of rollup summary fields on Account summarizing two fields of Opportunity will do the same. Below is the screenshot of a sample Rollup field I am trying to create. You can also put the criteria that which records of Opportunity do you want to consider when rolling up.

Try the Rollup field and it should do the work.

enter image description here

  • Thanks Nitish, I'm doing this in a Apex batch for an App, as this is part of a broader function. That is why I was looking at SOQL. Commented Jun 26, 2017 at 17:20
  • ok, in that case, use SOQL only, and try to use map collection. You will take a list of Accounts in a list, and query the opportunities which are related to only those account with additional filters in where clause. This will make your query more optimized and the number of records retrieved will be lesser. Commented Jun 26, 2017 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .