grouping query results by months

Question

I have a coldfusion query result, which contains only dates

like

2015-07-14 00:00:00.0   
2015-07-22 00:00:00.0   
2015-07-24 00:00:00.0   
2015-07-27 00:00:00.0   
2015-08-04 00:00:00.0   
2015-08-05 00:00:00.0   
2015-08-15 00:00:00.0   
2015-09-01 00:00:00.0   
2015-09-02 00:00:00.0   
2015-09-21 00:00:00.0   
2015-10-14 00:00:00.0   
2015-12-10 00:00:00.0   
2016-01-13 00:00:00.0 

I want to display query results grouping them on months basis

e.g Month's name as first column and then dates of that month per row. I have no idea how to group the query in this sitution.

Answer 1

Update your query to have a year and month column. You don't specify your DMBS but for MSSQL you would use the year(), month(), and day() functions. Make sure your query is ordered by year, month and day otherwise the grouping will not working properly. ColdFusion also has a built in function called monthAsString() to cast an integer to the month name.

SELECT year(datecolumn) AS Year, month(datecolumn) AS month, day(datecolumn) AS day, other, columns
FROM mytable
WHERE x = y
ORDER BY year, month, day

Output as

<cfoutput query="myquery" group="year">
  <cfoupt group="month">
    #monthAsString(month)#
    <cfoutput group="day">
      #day# #other# #columns#
    </cfoutput>
  </cfoutput>
</cfoutput>