Doubt in HQL when doing Sum function

Question

I have a doubt in hql.

My system has 3 tables Tabela1 and table 2 and table 3

table 3 stores the primary key records of Table 1 and table2 for making relationships.

Table 1 can contain N associations with table 2 but table 2 can only belong to 1 record of table 1;

I want to make a select that returns the sum of a field that stores an integer, a date of the table2 and the id of Tabela1.

When I run this Hql it only works correctly if "b. field" is only otherwise it does not perform the sum function correctly because in select.

select distinct a.id,sum(b.valor) ,b.campo from entidade1 a inner join entidade2 b;

I know this occurs because all the different values are returned when making the sum, but I do not know how to correct. Is there any way?

Answer 1

Friend, to make the sum sum() you need to group group by.

I think your query would be like this:

select a.id,sum(b.valor) ,b.campo from entidade1 a inner join entidade2 b GROUP BY a.id, b.campo

Clause GROUP BY you don’t need the distinct.

Follow the documentation from Hibernate:

https://docs.jboss.org/hibernate/orm/3.3/reference/pt-BR/html/queryhql.html#queryhql-grouping