1

I am using below queries to get two different count

Integer countActive = [SELECT Count() from Account where Status__c = 'Active' And ProviderId__c LIKE '%00%'];
Integer countInactive = [SELECT Count() from Account where Status__c = 'Inactive' And ProviderId__c LIKE '%00%'];

Is there a way to get the count in single query?

0

3 Answers 3

4

We can have this using a single query using group by and the aggregateresult as below

list<aggregateresult> agr = [select count(id), status  from account where ProviderId__c LIKE '%00%' group by status ];
integer active =  (integer)(agr[0].get('Status') == 'Active'? agr[0].get('expr0') : agr[1].get('expr0'));
integer inactive = (integer)(agr[0].get('Status') == 'Inactive'? agr[0].get('expr0') : agr[1].get('expr0'));
system.debug('Active '+active);
system.debug('Inactive '+inactive);
1

We can not do same with two criteria in single query. The best and optimized way to achieve this:

list<account> lstAcc = new list<account>([SELECT Status__c from Account where ProviderId__c LIKE '%00%' order by Status__c]);
integer ActiveCount = 0;
for(account objAcc:lstAcc){
    if(objAcc.Status__c== 'Active'){
        ActiveCount++;
    }else{
        break;
    }
}
Integer InActiveCount = lstAcc.size()-ActiveCount;
1

You can do a query with Group by clause.

List<AggregateResult> results = [SELECT Count(Id), Status__c FROM Account WHERE ProviderId__c LIKE '%00%' GROUP BY Status__c]

and then get the active and inactive status values.

You must log in to answer this question.

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