Sum a List of averages in spring boot jpa query

Question

I have this query thats return a list of averages

@Query("SELECT AVG(p.quantity) FROM Position p GROUP BY p.client.id") public List<Float> findAVGPositions();

But I want to return SUM of averages like this, but it doesn't work

@Query("SELECT SUM(averages) FROM (SELECT AVG(p.quantity) as averages FROM Position p GROUP BY p.client.id)") public Float findSumAVGPositions();

Can you help me? thanks

question from:https://stackoverflow.com/questions/65617511/sum-a-list-of-averages-in-spring-boot-jpa-query

Answer 1

Unfortunately, in JPQL, nested selects are not permitted in FROM clause. They are allowed only in SELECT and WHERE.

You have two approaches available.

(1) Use findAVGPositions and calculate sum in Java

    findAVGPositions().stream().reduce(0f, Float::sum);

(2) Use a native SQL query

    @Query(value = "SELECT SUM(averages) " +
                   "FROM (SELECT AVG(p.quantity) AS averages " +
                         "FROM position p " +
                         "GROUP BY client_id) AS averages_select",
            nativeQuery = true)
    public Float findSumAVGPositions();

Depending on the database you use, AS averages_select alias might be needed or not (PostgreSQL requires it, even if it's not used).

Please, pay attention to use the correct names of a database table for Position entity and a database column for a foreign key client.id.

In my example, I assumed a standard mapping convention: position as a table name, and client_id as a foreign key column.