Undesirable result - Query SQL

Asked

Viewed 63 times

3

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

My table data movement:

inserir a descrição da imagem aqui

My query:

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

Man outworking:

inserir a descrição da imagem aqui

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?

1 answer

5


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

  • It worked. User date(m.data).

Browser other questions tagged

You are not signed in. Login or sign up in order to post.