hi
i have to write a pretty complex query which use aggregate functions for sum calculations.i need to write sub query inside aggregate function count().i am using an appropriate constructor to select new instances.
i have written the following query . please analyse it and give me suggestion. its urgent.


String stringQuery = "select new PeriodicallyLogonDto(" +
"select distinct pl1.employee from PeriodicallyLogonDto pl1," +
"count(from PeriodicallyLogonDto pl2 where pl2.logonStatus=:paramP)," +
"count(from PeriodicallyLogonDto pl3 where pl3.logonStatus=:paramA)," +
"count(from PeriodicallyLogonDto pl4 where pl4.logonStatus=:paramL)," +
"count(from PeriodicallyLogonDto pl5 where pl5.logonStatus=:paramV)) " +
"from PeriodicallyLogonDto dto where dto.logonDateType between :param2 " +
"and :param3";

Query query = session.createQuery(stringQuery);
query.setString("paramA", "A");
query.setString("paramL", "L");
query.setString("paramV", "V");
query.setString("paramP", "P");
query.setDate("param2", dto.getPeriodFromType());
query.setDate("param3", dto.getPeriodToType());

return query.list();

Unfortunately, any subqueries are not allowed in new Foo(). This might change with Hibernate3. Use a createSQLQuery().