0

How do i get this into one query ? I need to count total no of opportunities which are closed won and closed lost . Have written two queries and want to make it one with one variables .

      public integer Y
      {
        get
       {
          return Y;
       }
       set
       {
          String q='select COUNT(id) from opportunity where StageName=\'Closed Won\'';
          Y=Database.countQuery(q);
          System.debug('check Y' + Y);
       }

       }    

   public integer X
  {
  get
   {
      return X;
   }
  set
  {
      String q='select COUNT(id) from opportunity where StageName=\'Closed Lost\'';
      X=Database.countQuery(q);
      System.debug('Check X' + X);
  }

   }    

I'm sorry, it was a typo . Want it with one query and one variable.

3

From my understanding of your question, you would like to 'combine' the queries into a single query. You can do that by using the 'IN' clause of SOQL. After that you could just sort them manually in a for-loop.

Here's an example.

public class TestClass {

    public Integer Y {
        get; set;
    }

    public Integer X {
        get; set;
    }

    public TestClass() {

        List<Opportunity> opportunities = [SELECT Id FROM Opportunity WHERE StageName IN ('Closed Lost', 'Closed Won')];

        Integer lostCount = 0;
        Integer wonCount = 0;

        for (Opportunity opp : opportunities) {
            if (opp.StageName == 'Closed Lost') {
                lostCount++;
            }

            if (opp.StageName == 'Closed Won') {
                wonCount++;
            }
        }

        Y = wonCount;
        X = lostCount;

    }
}
1
  • 2
    SELECT Count(ID), StageName FROM Opportunity WHERE StageName IN ('Closed Won','Closed Lost') GROUP BY StageName or even SELECT Count(ID), IsWon FROM Opportunity WHERE IsClosed = TRUE GROUP BY IsWon
    – sfdcfox
    Jul 11 '14 at 12:20
3

Try this:

Set<String> stageNames = new Set<String>('Closed Won', 'Closed Lost');

String q = 'SELECT Count(Id) FROM Opportunity WHERE StageName IN ' + stageNames;

Y = Database.countQuery(q);

Updated with your intention to make the code as clean as possible, I've shipped the query out to a helper method:

public integer Y {

    get {

        return Y;

    } set {

        Y = xySetter(y);
    }
}    

public integer X {

    get {

        return X;

    } set {

        X = xySetter(x);
    }
} 

private Integer xySetter (String char) {

    Integer retInt = 0;

    if (char == 'X' || char == 'Y') {

        String q = 'SELECT Count(Id) FROM Opportunity WHERE StageName = ';

        if (char == 'X') {

            q += '\'Closed Lost\'';

        } else if (char == 'Y') {

            q += '\'Closed Won\'';
        }

        retInt = Database.countQuery(q);        
    }

    return retInt;
}
4
  • Could you please help me with the modification in my code with the help of this code . Where do i place this ?
    – JJoseph
    Jul 11 '14 at 12:07
  • I'm not quite sure what you mean by 'Have written two queries and want to make it one with one variables .' Jul 11 '14 at 12:18
  • I've given two variables X and Y for Closed won and Closed lost .The objective is to minimise my code with one variable and one query and your query can work but how can i make it ? Also modified my question. Thanks
    – JJoseph
    Jul 11 '14 at 12:21
  • Have updated my answer. Jul 11 '14 at 12:29
1

I think that your query is these

String q = 'Select count(Id) number , IsWon FROM Opportunity WHERE IsClosed = true group by IsWon';
1
  • your query is working but how would i make it to one variable ?
    – JJoseph
    Jul 11 '14 at 12:17

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.