Q:

How to extract year,month from a date and multiple columns using projections with group by and given as alias name by using hibernate Criteria

I have to get some count of id by using groupby and multiple columns also using projection list and I want to set alias name for some of the column, now I am giving sample of sql query 
String sql ="select createdby,count(id) as created_by_count,EXTRACT(YEAR FROM createdon) as year,to_char(createdon, 'Month') as month from projects where createdon BETWEEN '"+fromDate+"' AND '"+todate+"' group by createdby,EXTRACT(YEAR FROM createdon),to_char(createdon, 'Month')  order by createdby,year,month"

I am writing like this with crietria 
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("id.createdby"));
projList.add(Projections.groupProperty("id.createdby"));
projList.add(Projections.sqlProjection( "yeard(createdon) as year", new  String[] {"year"}, new Type[] {StandardBasicTypes. INTEGER} ));  
crit.setProjection(projList);
Criterion cn = Restrictions.between("createdOn",fromDate,todate);
crit.add(cn);
crit.addOrder(Order.asc("createdBy"));
crit.setResultTransformer(Criteria. ALIAS_TO_ENTITY_MAP);
List projectCount = crit.list();

Please anyone help me on this. 

A:

I have done almost apart from only one field 
Criteria crit = sessionFactory.getCurrentSession().createCriteria(Project.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.groupProperty("createdBy").as("createdBy"));
    projList.add(Projections.count("id").as("created_by_count"));
    projList.add(Projections.sqlProjection( "EXTRACT(YEAR FROM createdon) as year", new String[] {"year"}, new Type[] {StandardBasicTypes. INTEGER} ));
    projList.add(Projections.sqlProjection( "to_char(createdon, 'Month') as month", new String[] {"month"}, new Type[] {StandardBasicTypes. STRING} ));
    crit.setProjection(projList);
    Criterion cn = Restrictions.between("createdOn",fromDate,todate);
    crit.add(cn);
    crit.addOrder(Order.asc("createdBy"));
    crit.setResultTransformer(Criteria. ALIAS_TO_ENTITY_MAP);
    List projectCount = crit.list();
    return projectCount;

only one thing is pending which is how to extract year or month from date , that also I have tried using sql projection which is working but I want to use groupby on both things for that I dont know how to do please anyone help me on this

