SOQL - how to group by CALENDAR_MONTH

Question

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

Answer 1

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'));
}