I'm new to SOQL queries. I am trying to write a query that will calculate the total number of UNIQUE products a user has applied to their history of opportunities. The goal being to assess their product diversity usage.

Example: A salesperson has 5 opportunities

  • Oppty 1 - Product A
  • Oppty 2 - Products B, C
  • Oppty 3 - Products A, C
  • Oppty 4 - Products A
  • Oppty 5 - Product C

Their total product count would be A(3), B(1), C(3). Their UNIQUE products are A,B,C = 3 Products

What would be the best way to calculate this across 50,000 opportunity records considering any governor limits?

Also, can a query like this be written as a SOQL query that can be run in a tool like dataloader or workbench?

  • 2
    can you try out writing queries and post along with your question. Secondly how salesperson is linked to Oppty? Commented Dec 24, 2016 at 17:23

3 Answers 3


You can use following SOQL -

Select COUNT_DISTINCT(Product2Id) From OpportunityLineItem

If sales person is determined by CreatedBy then you can group by above query on CreatedBy field to get the unique product count by each Salesperson. e.g. -

Select COUNT_DISTINCT(Product2Id), Createdby.Name From OpportunityLineItem Group by CreatedBy.Name
  • 1
    If I am not wrong, this prints only sales rep name and number of distinct products he added but not how many times he added each product. As per OP, he wants the product and the number of times it was added by the sales rep.
    – javanoob
    Commented Dec 24, 2016 at 20:40
  • LakhanP' s answer provided the results I was expecting. I am too new to comment on the other answers but thank all of you for your knowledge and input !
    – Soapmuseum
    Commented Dec 26, 2016 at 15:43
  • There are limits it seems when trying to query 50000 opportunities. Here is what I did based on his comment. Not sure how to get the full results without limiting Select COUNT_DISTINCT(ProductId__c),Createdby.Name From MasterCard_Opportunity_Product__c WHERE CreatedDate > 2016-10-01T00:00:00Z Group by CreatedBy.Name LIMIT 200
    – Soapmuseum
    Commented Dec 26, 2016 at 15:51
  • Hi @Soapmuseum, Salesforce is a multi tenant platform therefore it has some governor limits for good reasons. In your case, LIMIT may not help to overcome this issue salesforce.stackexchange.com/questions/7439/… You will have to restrict your where clause e.g. CreatedBy.userRoleId In :salesPersonRole
    – LakhanP
    Commented Dec 26, 2016 at 19:27

Typically when I've been asked to calculate this sort of KPI it was measured by the Opportunity owner. If you want to count the number of times each product has been ordered for each rep, you need to use a GROUP BY clause on PricebookEntry.Product2Id and Opportunity.OwnerId. If you don't care about the Id values and just want names, you would change it to PricebookEntry.Product2.Name and Opportunity.Owner.Name.


List<AggregateResult> products = [
    SELECT PricebookEntry.Product2Id, Opportunity.OwnerId
    FROM OpportunityLineItem
    GROUP BY PricebookEntry.Product2Id, Opportunity.OwnerId


List<AggregateResult> products = [
    SELECT PricebookEntry.Product2.Name product, Opportunity.Owner.Name owner
    FROM OpportunityLineItem
    GROUP BY PricebookEntry.Product2.Name, Opportunity.Owner.Name

If you're looking to export this data, I suggest you explore using Reports to aggregate this data. They're already purpose built for export, and you can get the results as a .csv or .xls.


The following is one shot SOQL that provides the Product Group Count across all the Opportunity Line Items for all Opportunities for a given Opportunity Owner. Please provide a valid Opportunity.Owner.Id in the where clause of the query.

select Product2.Name,COUNT(Product2.Id) from OpportunityLineItem
where Opportunity.Owner.Id='<>' group by Product2.Name

You must log in to answer this question.

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