SQL generated by Hibernate invalid for MS SQL

Issue

Hibernate (HQL) generated the following SQL for which I inserted the parameters:

select 
sum(activity0_.calculated_work) as col_0_0_
, employee2_.id as col_1_0_
, projectele1_.id as col_2_0_ 

from 
activity activity0_ 
inner join generic_object activity0_1_ on activity0_.id=activity0_1_.id 
left outer join project_element projectele1_ on     activity0_.project_element_id=projectele1_.id 
left outer join employee employee2_ on activity0_.owner_id=employee2_.id 
left outer join org_unit orgunit3_ on employee2_.org_unit_id=orgunit3_.id 

where 
activity0_1_.deleted=0 and 
activity0_.client_id=22 

 group by 
 employee2_.id order by SUM(activity0_.calculated_work) DESC

Error message: Column 'project_element.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I executed this SQL directly in the SQL Server Studio with the same result. I commented this line:

, projectele1_.id as col_2_0_ 

The SQL was then accepted by the SQL Server

The table project_element definitely has a column with the name id it is also referenced in the LEFT OUTER JOIN and there this column is not causing an error.

Removing the alias projectele1_ had no effect.

To me this looks like a really simple SQL statement. I cannot imagine what is wrong with it and the error message is not helping at all. Any ideas what could be wrong with the SQL?

Solution

Your SQL syntax is wrong.If you add projectele1_.id to group by clause it will work.Only aggregate functions work in select statement with group by clause.Or if you remove projectele1_.id from select it will work fine.

Answered By - Afsun Khammadli