sql-serverhibernatejpahibernate-native-query

Hibernate Native query returned duplicated result in result array with group by added in query


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


Solution

  • 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