3

I need to display related opportunities in lead page with some requirements like order by unit potential and display only highest unit potential record of the opportunity.means I want to display distinct opportunity records

So I tried to use Count_Distinct but I am not able to parse query.

How can I modify the below query to get the data I want?

Select Id,Number_of_Units__c,Name,StageName,Type,Billing_City__c,Opp_Sic_Code__c from 
Opportunity where Billing_City__c=:cityset AND Opp_Sic_Code__c=:Sicset AND 
StageName='Closed Won' ORDER BY Number_of_Units__c Desc Limit 10

Opportunity Map

I need to display only two opportunities in above scenario first one and third one,opportunity test-- & Internal.Because test-- has the highest unit potential for the account Sample and internal is related to other account

  • can you provide an example with data what you are looking for – Santanu Boral Jun 6 '17 at 16:48
  • Do you want to get the opportunity with highest unit potential related to an account? – SfdcBat Jun 6 '17 at 18:35
  • Yes @SfdcBat but I need to display only one opportunity from an account. – Pavan tej Jun 6 '17 at 18:37
  • @SantanuBoral My question has been updated please check once. – Pavan tej Jun 7 '17 at 16:09
  • check my answer, hopefully it will work for you – Santanu Boral Jun 7 '17 at 16:37
2

There will not be direct SOQL query which can meet your requirement.

Here is the approach.

Put ORDER BY AccountId,Number_of_Units__c Desc in SOQL

  • Since you are retrieving records in DESC, so highest value for an Account will be displayed first and that will be added to the map.

  • Now, next time, for the same account, if Opportunity record is coming then that will be ignored.

  • finally take the mapAccountWithOpporty.values() which will have Opportunity with highest Number_of_Units__c, perform whatever you want to do.
//this map holds AccountId with that Opportunity which has highest Number_of_Units__c
Map<Id, Opportunity> mapAccountWithOpporty = new Map<Id, Opportunity>();

List<Opportunity> lstOppty = [Select Id, AccountId, Number_of_Units__c,Name,StageName,Type,Billing_City__c,Opp_Sic_Code__c 
                                from Opportunity 
                                where Billing_City__c=:cityset 
                                AND Opp_Sic_Code__c=:Sicset 
                                AND StageName='Closed Won' 
                                ORDER BY AccountId,Number_of_Units__c Desc];
for(Opportunity opty:lstOppty)
{
    if(!mapAccountWithOpporty.containsKey(opty.AccountId))
    {
        mapAccountWithOpporty.put(opty.AccountId, opty);
    }
}
| improve this answer | |
  • Thanks. Order By is a great idea. May I ask if there is any reason for using Limit 10? – SfdcBat Jun 7 '17 at 17:16
  • 1
    Limit is not required, I have used your limit clause, I have removed at my updated code – Santanu Boral Jun 7 '17 at 17:20
  • 2
    Gotcha. Hope the OP finds this helpful to solve his problem. – SfdcBat Jun 7 '17 at 17:21
2

If you want to return all of the fields you have in your query, you likely want to avoid aggregate queries, which is the only place you can use the COUNT_DISTINCT function. Any such attempt would yield:

Field must be grouped or aggregated: ...

You can use post-processing to get the count of distinct AccountId values using a Set. Don't forget to add this field to your query!

List<Opportunity> records = [/*query*/];
Set<Id> accountIds = new Set<Id>();
for (Opportunity record : records)
    accountIds.add(record.AccountId);
Integer uniqueAccountIdCount = accountIds.size();
| improve this answer | |
0

You can do it by two ways.

1) Apex : Programmatic way, as suggested by Adrian Larson.

2) Query using GROUP BY aggregate function. I have shown like below, you need to add all your custom object shown in your posts.

enter image description here

Possible duplicates of the How to select Unique values in SOQL . How to get unique values of two fields in one SOQL?.

| improve this answer | |
  • 1
    My understanding is - OP wants to get the Id of Opportunity which has greatest value in Number_of_Units__c field. Example if an Account A1 has O1,O2,O3 as its opportunity. OP wants to get the Opportunity that has highest value in Number_of_Units__c for A1. Similarly for each account. – SfdcBat Jun 6 '17 at 22:04
  • yes and finally i need to display order by Number_of_Units__c will you please guide me @SfdcBat – Pavan tej Jun 7 '17 at 8:21
  • @Pavantej Please check Santanu's anwer. Hope it gives you the solution – SfdcBat Jun 7 '17 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.