Group By doesn't load data

gstasica · **Posted:** Mon Jul 10, 2006 4:17 pm

hi,

I'm stuck with simple problem of group by clause. I've a mapping document like below:

<class name="Report" table="Report">
<id name="id" type="integer" unsaved-value="null">
<column name="id" />
<generator class="native" />
</id>
<many-to-one name="project" column="Project_id" class="Project" />
<many-to-one name="controlArea" column="ControlArea_id" class="ControlArea" />

<property name="direct" type="integer">
<column name="Direct" not-null="true" />
</property>

...

My HSQL is like below
Query=session.createQuery("select r.project,r.controlArea,sum(r.direct) from Report r group by r.project,r.controlArea")

For above case I get an error that r.project.id is not contained in group by clause. !!! What's wrong since I've a property for project in mapping document.

...
The other problem is that with Spring framework and HibernateDaoSupport instead of objects fetched from the query (project,controlArea) I get ID's if group by clause is modified in this way ... group by r.project.id

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I don't think that you can group by entities. The best you can do is group by id, then join the result to the correct entities. Something like

Code:

select r, (select sum(q.direct) from Report q
           where q.project = r.project and q.controlArea = r.controlArea)
  from Report r

Not certain that that will work, but I think some variation on it will. For example, you may have to use q.project.id etc. in the subselect's where clause.

gstasica · **Posted:** Mon Jul 17, 2006 4:26 pm

thanks for your input. In the meantime I've checked once again documentation and found information that one can not group by entities :(