# SOQL Multiple COUNT 's with different conditions

I have tried a bunch of different ways of getting a result about the age in only one query. I want to count the contacts that are between 20 to 29 years old and count in another column the contacts that are between 30 to 40 years old in only one query, but I made two queries. Here is the first:

``````SELECT COUNT(Id) FROM Contact WHERE Age__c >= 20 AND Age__c <= 29
``````

And I have another query like this:

``````SELECT COUNT(Id) FROM Contact WHERE Age__c >= 30 AND Age__c <= 40
``````

My question is: is there a way to get this 2 parameters in only one query? if so, how? please help

Without a formula field, you could group by age, then sum them up in your application:

``````SELECT COUNT(Id), Age__c FROM Contact GROUP BY Age__c WHERE Age__c >= 20 AND Age__c <= 40
``````

This would give you results like:

``````50     20
25     21
29     22 ...
``````

Then, in your code, simply add the sums together for each specific range.

I don't believe this is possible as there is no real way to group the results as of now. You could only return multiple counts if you are grouping by something.

You could do this if you add the logic to a formula field on the contact. So you would have a formula field on the contact that checked the age and put them into a range. So this field could be 10-19, 20-29, 30-39, etc. Then you could use that formula field to group your results like below. (assuming the field is called Age_Range__c)

``````SELECT COUNT(Id) myCount, Age_Range__c FROM Contact Group By Age_Range__c
``````

The results it would return would be something like this

Age_Range__c myCount

10-19 100

20-29 150

30-39 200

40-49 150

• First of all thanks for your answer, I've tried that you just suggest. My Formula returns a text value. Here is: `IF(AND(Age__c >= 20, Age__c <= 29),"20-29", IF(AND (Age__c >= 30, Age__c <= 40), "30-40", "very young or too old"))` Ok, this works at this point but when I try the query that you suggest in Workbench it send me an error: ERROR at Row:1:Column:69 field 'Age_Range__c' can not be grouped in a query call any suggest? am I doing something wrong? Commented Aug 7, 2014 at 22:21
• I've found that there is limitation like formula fields can't be used in group by clause. Commented Aug 7, 2014 at 23:25

For 2 specific ages:

``````SELECT sum(CASE WHEN Age_C between 20 and 29 THEN 1 ELSE 0 END) as Age2029,
sum(CASE WHEN Age_C between 30 and 40 THEN 1 ELSE 0 END) as Age3040
from contact
``````

OR for group by 10 years

``````select (convert(int,(Age_C/10))*10) as Age_from,
(convert(int,(Age_C/10))*10+9) as Age_to,
count(Age_C) as numberContacts
from contact
group by convert(int,(Age_C/10))
``````
• This would work if this was SQL, but `CASE` and `CONVERT` are not available in SOQL. Commented Aug 8, 2014 at 10:37