skip to Main Content

In the following scenario, there are 3 tables in a PostgreSQL database (tables and values are simplified for clarity). I am trying to get the following values:

  • sum of the amounts for records (101, 102, 103) in the parent table for 2020 year (1300)
  • count of the corresponding records in parent-a (4)
  • count of the corresponding records in parent-b (2)

parent:

| id   |  name     | amount    | year  |
----------------------------------------
| 101  |  Henry    | 300       | 2020  |
----------------------------------------
| 102  |  Carol    | 100       | 2020  |
----------------------------------------
| 103  |  Tom      | 900       | 2020  |
----------------------------------------
| 104  |  Fredrick | 150       | 2022  |
----------------------------------------
| 105  |  Mary     | 180       | 2023  |
----------------------------------------

child-a:

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-a      |
--------------------------------------
| 2    |  101        | value-b       |
--------------------------------------
| 3    |  101        | value-c       |
--------------------------------------
| 4    |  102        | value-d       |
--------------------------------------

child-b:

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-x       |
--------------------------------------
| 2    |  102        | value-y       |
--------------------------------------
| 3    |  105        | value-z       |
--------------------------------------
| 4    |  106        | value-t       |
--------------------------------------

For this, I created the following query (I will revert it to HQL). Because, when I join tables, the amount values are multiplied and when I group records in left join via a sub clause, it gives error in HQL as HQL does not support sub clause in JOIN:

select (
       COALESCE(sum(p.amount), 0),
       (
          select count(ca) from child_a ca left join
             parent p on ca.parent_id = p.id
       ),
       (
          select count(cb) from child_b cb left join
             parent p on cb.parent_id = p.id
       )
    ) from parent p where p.year = 2020

Is there a better way to improve this that also works qith HQL as well?

2

Answers


  1. Two left joins can compute the result. For example:

    select sum(s), sum(a.c), sum(b.c)
    from (select id, sum(amount) as s from parent where year = 2020 group by id) p
    left join (select parent_id as id, count(*) as c from child_a group by id
              ) a on a.id = p.id
    left join (select parent_id as id, count(*) as c from child_b group by id
              ) b on b.id = p.id
    
    Login or Signup to reply.
  2. Try this:

    I suppose you use three objects named Parent, ChildA, ChildB to map the three Postgres table

    SELECT SUM(p.amount), 
        (SELECT COUNT(1) FROM ChildA ca where ca.parentId = p.record) as childAElement, 
        (SELECT COUNT(1) FROM ChildB cb where cb.parentId = p.record) as childBElement
    FROM Parent p
    WHERE p.record in (101, 102, 103) and p.year = 2020
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search