0

I'm working in Spring for a college project. I have to develop a system that allow users to create "meetings". I've mapped the relationship between User and Rendezvous(Meeting) like this:

User (1) <--- (0...*) Rendezvous

@Entity
@Access(AccessType.PROPERTY)
public class Rendezvous extends DomainEntity {

private User                        user;


@NotNull
@Valid
@ManyToOne(optional = false)
public User getUser() {
    return this.user;
}

public void setUser(final User user) {
    this.user = user;
}

}

I need to know the average and the standard deviation of rendezvouses created per each user, so I'm trying to create a query to do that.

I need to make an equivalent query to this one in mySQL but in JPQL

    select avg(rendezvouses) from (
       select user_id, count(*) as rendezvouses 
          from rendezvous group by user_id
    ) nestedQuery;

1 Answer 1

1

If i understand correctly you need to get the average rendezvous for all users and the number of rendezvous for each user.

According to the JPA 2.1 specifications defined in JSR-338 - Java Persistence 2.1, JPQL the argument for the AVG function must be numeric.

Further more, you won't be able to reference a subquery in JPQL (without having / exists construct AFAIK).

Therefore you should create a query to get the total count of rendezvous

select count(r.id) from rendezvous r

Then divide by the number of users.

Then you can get the number of rendezvous for each user :

select count(r.id) from rendezvous r group by r.user.id

Given this, i would however recommend to use sql queries (native queries) rather than jpql query. JPA / JPQL is better for handling objects rather than finely tuned sql queries.

For instance, you could get easily the standard deviation :

SQL - STDEVP or STDEV and how to use it?

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.