Unwanted Result - SQL Query

Question

Unwanted Result - SQL Query

Navigation

#1 by (5 votes)

3

Hello. I'm a problem and I can not see a way to solve it.

My data from the move table:

Myquery:

selectavg(m.valor)fromMovimentacaomwherem.tipo='SAIDA'groupbym.data;

Myresult:

I can not group the average by date (day). Even if I use distinct . Always returns all dates. If event I group by holder works, less with date. This is done by searching the JPA and also via the database.

The table was generated by JPA / Hibernate using Calendar for the date.

@Temporal(TemporalType.TIMESTAMP)
private Calendar data;

Does anyone see the problem?

sql java jpa

asked by anonymous 04.09.2018 / 02:30

1 answer

Error instantiating objects in python OO Binary search tree and binary search (Binary search tree)

score 5 · Accepted Answer

AVG is an aggregate function, so it will aggregate the value you put into the function. In your case the average value. What you want is to aggregate the average of the values by the day, the way you did it, you are trying to group by the date that is timestamp (Year / Month / Day / Hour / Minute / Second / Millisecond).

To group by day, you have several forms:

The first way, I recommend, is to transform your column into a Data type (Day / month / year) Using the following annotation:

@Temporal(TemporalType.DATE)
private Date data;

For more information: link

Another way is to get the date through some SQL function. In the case of MYSQL:

SELECT avg(m.valor) 
FROM Movimentacao m 
WHERE m.tipo='SAIDA' 
GROUP BY DATE(m.data);

For more information: link

And finally, the last way would be to get via HQL, I recommend creating a function, or declaring some function to extract just the date, similar to MYSQL's DATE (). For more information: link