Search code examples
javapostgresqlhibernatehql

subquery for aggregate and group by

Good afternoon, I ran into such a problem and I don’t quite understand how to solve it in the hql query language, I have a request from several parameters that need to be returned all in the answer, I need to aggregate 3 of them, I need to group by one of them! for the other two, you just need to get the content. I understand how postgresql works, but I don’t know how to solve this problem, because when grouping, it requires all fields with which mathematical operations have not been performed.

@Query("SELECT new value.Value " +
            "(SUM(p.t1), SUM(p.t2), p.t3, SUM(p.t4), p.t5, p.t6) " +
            "FROM P p " +
            "WHERE p.tId = :tId " +
            "GROUP BY p.t6 ")

Maybe I need to do subquery, but I don't know how to do it in HQl

Solution

  • If I understand you correctly, you want to sum some fields (t1, t2, t4) grouped by t6 and at the same time show fields (t3, t5, t6) without sum. I can write for you only SQL query how to do it and this query you can use in your hibernate code easily.

    Fox example:

    select 
    sum(p.t1) over (partition by p.t6), 
    sum(p.t2) over (partition by p.t6), 
    p.t3, 
    sum(p.t4) over (partition by p.t6), 
    p.t5,  
    p.t6  
from P p
WHERE p.tId = :tId