SOQL Multiple COUNT 's with different conditions

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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))