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?
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
Answer Checked By – Gilberto Lyons (Easybugfix Admin)