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
    Commented May 6, 2016 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 Commented May 6, 2016 at 10:52

1 Answer 1

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 Commented May 6, 2016 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
    Commented May 6, 2016 at 11:06
  • unfortunately it doesn't , so I guess what I'm trying is not achievable in SOQL Commented May 6, 2016 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
    Commented May 6, 2016 at 11:12

You must log in to answer this question.

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