Undesirable result - Query SQL

Question

Hello. I’m a problem and I don’t see a way to solve.

My table data movement:

My query:

select avg(m.valor) from Movimentacao m where m.tipo='SAIDA' group by m.data;

Man outworking:

I cannot group the average by date(day). Even if I use distinct. Always returns all dates. If I group by holder works, less with date. This occurs by searching for JPA and also via data bank.

Table was generated by JPA/Hibernate using Calendar for date.

@Temporal(TemporalType.TIMESTAMP)
private Calendar data;

Someone sees the problem?

Answer 1

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

To group by day, you have various shapes:

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

@Temporal(TemporalType.DATE)
private Date data;

For more information: https://docs.oracle.com/javaee/6/api/javax/persistence/TemporalType.html

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: https://stackoverflow.com/questions/21151335/select-date-from-timestamp-sql

And lastly, the last way would be to take via HQL, recommend creating a function, or declaring some function to extract only the date, similar to DATE() from MYSQL. For more information: https://stackoverflow.com/questions/17826241/hql-query-based-on-date-on-a-timestamp-column