0

I have the following SOQL queries

Double counter1= [Select  count( )  from Opportunity where createdDate >=  Last_n_DAYS:30 and isClosed=true and Stagename!='Rejected'  ];
Double counter2= [Select  count( )  from Opportunity where createdDate >=  Last_n_DAYS:30 and isClosed=true and (StageName='Pending Payment' or Stagename ='Complete')];

System.debug((counter2 / counter1) *100);

Is it possible to combine them into 1,which would return the same result as (counter2/counter1) *100 ?

NOTE: I can not use any intermediate variables, the end result has to come from a single query statement.

2
  • Select count( ) from Opportunity where createdDate >= Last_n_DAYS:30 and isClosed=true AND (Stagename!='Rejected' AND (StageName='Pending Payment' or Stagename ='Complete')) Please test and verify
    – C0DEPirate
    May 6 '16 at 10:45
  • @CODEPirate your answer is the same as my counter2. I want the end result to be a percentage of counter2 out of coutnter1 and I am not sure how to achieve this in SOQL May 6 '16 at 10:52
6

You could use an aggregate query:

Integer counter1 = 0;
Integer counter2 = 0;
for (AggregateResult ar [
        select Stagename n, count() c
        from Opportunity
        where createdDate >= Last_n_DAYS:30
        and isClosed = true
        group by Stagename
        ]) {
    String n = (String) ar.get('n');
    Integer c = (Integer) at.get('c');
    if (n != 'Rejected') counter1 += c;
    if (n == 'Pending Payment' || n == 'Complete') counter2 += c;
}
System.debug((counter2 / counter1) * 100);

This returns a count for every Stagename and uses the Apex loop to sum those counts appropriately.

4
  • Thanks for the answer @Keith C but I am trying to use the result of the query in Geckoboard hence it will only allow me to write 1 single query that gives me the end result. Hence I would not be able to utilize any intermediate variable. However I do appreaciate your support May 6 '16 at 11:03
  • @MarcZaharescu Best add that constraint to the question... does that product have a forum where you can ask this question?
    – Keith C
    May 6 '16 at 11:06
  • unfortunately it doesn't , so I guess what I'm trying is not achievable in SOQL May 6 '16 at 11:11
  • 1
    Does geckoboard allow you to pull in data from a webservice call? You could write a webservice in SF using the logic that Keith has here and returning the single value.
    – Doug B
    May 6 '16 at 11:12

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.