I am using Hibernate version 3. My table has 9 columns and my query looks like
select col1, count(col2), sum(col3), sum(col4) from table a
where a.col5= 'criteria1' and a.col6 = 'criteria2'
and a.col7 = 'criteria3' and a.col8 = 'criteria4' group by col9
col2 is the id and primary key of the table. When I run same query in query browser it gives me correct result. For example, if result returned in browser was test,10,300,500 Result returned through natove query was different ,and it was test,10,10,10
In native query result , 2nd column in result got duplicated to third and fourth columns. Observed this behavior only if group by is added to query. If I remove group by and sum on columns it returned correct results. I have researched on Google there seems to be an existing issue but no answer was found for this. Any help would be appreciated
Adding aliases to count
and sum
resolved the issue.
Fixed query looked like below one. changes are shown in bold.
select col1, count(col2) as count1, sum(col3) as sum1, sum(col4) as sum2 from table a
where a.col5= 'criteria1' and a.col6 = 'criteria2'
and a.col7 = 'criteria3' and a.col8 = 'criteria4' group by col9