Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
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? –  Aldo Escutia Aug 7 at 22:21
    
I've found that there is limitation like formula fields can't be used in group by clause. –  Aldo Escutia Aug 7 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))
share|improve this answer
    
This would work if this was SQL, but CASE and CONVERT are not available in SOQL. –  Alex Tennant Aug 8 at 10:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.