Hi, i wouldlike to use aggregate functions in HQL, and i got such problems while experimenting with the query...

i have a class Booking like

Booking(id, tutor,week,package,date,fee,term,note,student,order)

i wouldl like to do a query as such : "

"SELECT DISTINCT tutor, week, COUNT(DISTINCT student) AS numOfStudents
FROM Booking WHERE term=:tm GROUP BY
tutor,week"

problem 1 is - the query does not work because i got an error telling me that group by clause must include all variables of this Booking class, i e , i changed my query to

"SELECT DISTINCT tutor, week, COUNT(DISTINCT student) AS numOfStudents
FROM Booking WHERE term=:tm GROUP BY
tutor,week, id, package, date,fee,term,note,student,order"

and it works. however, it seems that the query produces a list of Booking objects, not only the variables tutor, week, and numOfStudents as specified in the SELECT clause. as a result i got 2nd problem

problem 2 - i actually defined another class called "Rota(tutor, week, numOfStudents)"

and i did
Rota t = (Rota) queryResult.get(0);

and i got a ClassCastException. i guess the reason is that the query produces a list of Booking objects other than the only selected variables as in the select clause; therefore i changed to
Booking b =(Booking) queryResult.get(0);

unsurprisingly it doesnt work i guess its because in the select clause i have another variable "numOfStudnets" which is not defined in Booking.

i dont know what to do... i certainly donot want to add a variable of "numOfStudent" to Booking class.. but i dont know any other solutions to this problems. seems HQL makes aggregate query much more complicated, or im doin this in totally wrong way?
all in all, all i want to do is to get the values of "tutor, week, and numberOfStudents" from the query, nothing else.

any ideas please help! thank you.

Maybe you can try this approach regarding the rota class that you have:

"SELECT DISTINCT new Rota(tutor, week, COUNT(DISTINCT student))
FROM Booking WHERE term=:tm GROUP BY
tutor,week, id, package, date,fee,term,note,student,order"


I haven't tried this in a test case but I guess it could work out for you. This way you should avoid the ClassCastException.

thanks so much! its working now!

hmm i still dont understand why the generated hql selects all variables of Booking other than the only specified in the query... isnt it more expensive in terms of the cost of query?

thanks

Usually colum reads are not so expensive (unless you are reading a LOB).