How to nest aggregation functions in JPQL

Question

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;

score 1 · Answer 1 · answered Feb 19 '18 at 15:22

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?

How to nest aggregation functions in JPQL

1 Answers1