count and group by using ExcecuteQuery

enter image description here

how to get count number with group by activationDate?

i tried this query

def mapQuery6= new HashMap()

def query6 = "SELECT count(al.id) as totalCount,al.activationDate from Card al where and TO_DAYS(al.activationDate) > TO_DAYS(:from) and TO_DAYS(al.activationDate) <= TO_DAYS(:to) GROUP BY al.activationDate "

mapQuery6.from = params.from
def to = new Date().parse('dd/MM/yyyy HH:mm:ss', params.to+ " 05:00:00")
to.setDate(to.getDate() + 1)

mapQuery6.to = to

println "query 6 = "+query6 

def activecard = Card.executeQuery(query6,mapQuery6)[0] 

println "activecard = "+activecard

i want the result like this

enter image description here

if iam using Group by al.activationDate the the result when i printed was

activecard = [1, 2014-06-17 20:27:11.0]

and if without group by al.activationDate the result was

activecard = [6, 2014-06-27 20:27:11.0]

so i want ge a count number every month.. example 2014-07-xx have 3 count, 2014-06-xx 2, and 2014-05-xx only 1

Solution

Try grouping by month like

GROUP BY MONTH(al.activationDate)