3

I am trying to create a custom chart in a visualforce page. I am using the results of a SOQL query as my data for the chart. I have this SOQL query:

SELECT CALENDAR_MONTH(Hire_Date__c) month_number,
COUNT(Id) num_employees FROM Employee__c
GROUP BY CALENDAR_MONTH(Hire_Date__c)

which returns:

CALENDAR_MONTH (Integer corresponding to month)  COUNT
8                                                8
12                                               1

However, I want it to return something for every month, and include a 0 if COUNT doesn't return anything. So I need a query that will return these results:

CALENDAR_MONTH  COUNT
1               0
2               0
3               0
4               0
5               0
6               0
7               0
8               8
9               0
10              0
11              0
12              1

How do I do this?

Thanks, Nick

4

I don't think you can run a query like that. But you can define a map of all the month numbers to zeros, then run your query and push the data into the map. Any missing months will not go into the map, so those values will remain at zero.

map<integer, integer> monthToCount = new map<integer, integer>{
  1 => 0,
  2 => 0,
  3 => 0,
  4 => 0,
  5 => 0,
  6 => 0,
  7 => 0,
  8 => 0,
  9 => 0,
  10 => 0,
  11 => 0,
  12 => 0
};
for (aggregateResult ar : [ SELECT CALENDAR_MONTH(Hire_Date__c) month_number,
                            COUNT(Id) num_employees FROM Employee__c
                            GROUP BY CALENDAR_MONTH(Hire_Date__c)])
{
   monthToCount.put( (integer) ar.get('month_number') , (integer) ar.get('num_employees'));
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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