Select Query of third table-CodePudding

I have these three tables, and each contract can have several units bound to it. What I need is the sum the field "area" of all units that are in the same contract.

The query should look like this. The last field "Price fraction" is going to be a calculated field, so I don't need it in the SQL query.

I've tried writing the query, but it always gives the wrong sum.

Select
Unit.description "Unit",
Unit.area "Area",
Contract.price "Contract Value",
(select sum(Unit.area) from Unit, ContractUnit where ContractUnit.unit_id = Unit.id) "area_sum"
from
Unit
JOIN ContractUnit ON ContractUnit.id_unit = Unit.id
JOIN Contract ON Contract.id = ContractUnit.contract_id
where Unit.id = :idUnit

CodePudding user response：

Let's dissect the requirement and break it up into steps.

Evaluation:

There are three tables:

Unit

Contract

ContractUnit

Unit and Contract entities have many-to-many relationship and hence there is the need of ContractUnit which is the associative entity (or bridge table).

Step 1: The following columns first needs listed: Unit_id , Area (from table Unit)

Contract_value(dollars) and contract_id (from table contract)

Actually , the final output will not need contract_id but it is still needed in the first step because sum_area is calculated over it. In the final output , we will not display it.

So we get it here:

SELECT u.id    unit,
       u.area  area,
       c.price contract_value,
       c.id    contract_id
FROM   unit u
       INNER JOIN contractunit cu
               ON u.id = cu.unit_id
       INNER JOIN contract c
               ON c.id = cu.contract_id

Output:

 unit | area | contract_value | contract_id
------ ------ ---------------- -------------
    1 |  340 |         400000 |           1
    2 |  540 |         400000 |           1
    3 |  130 |         560000 |           2
    4 |  503 |         560000 |           2
(4 rows)

Step 2 : we get the final value by doing a sum of area over contract_id on the output obtained above.


SELECT unit,
       area,
       subq.contract_value,
       Sum(area)
         OVER (
           partition BY contract_id) sum_area
FROM   (<earlier query> ) subq;

that is:

SELECT unit,
       area,
       subq.contract_value,
       Sum(area)
         OVER (
           partition BY contract_id) sum_area
FROM   (SELECT u.id    unit,
       u.area  area,
       c.price contract_value,
       c.id    contract_id
FROM   unit u
       INNER JOIN contractunit cu
               ON u.id = cu.unit_id
       INNER JOIN contract c
               ON c.id = cu.contract_id ) subq;

Result:

unit | area | contract_value | sum_area
------ ------ ---------------- ----------
    1 |  340 |         400000 |      880
    2 |  540 |         400000 |      880
    3 |  130 |         560000 |      633
    4 |  503 |         560000 |      633
(4 rows)

--- In case your RDBMS type/version do not support window function or even CTE , you can get the same result with this query ---

SELECT b.unit,
       b.area,
       b.contract_value,
       a.sum_area
FROM   (SELECT contract_id,
               Sum(area) sum_area
        FROM   (SELECT u.id    unit,
                       u.area  area,
                       c.price contract_value,
                       c.id    contract_id
                FROM   unit u
                       INNER JOIN contractunit cu
                               ON u.id = cu.unit_id
                       INNER JOIN contract c
                               ON c.id = cu.contract_id) subq
        GROUP  BY contract_id) a
       INNER JOIN (SELECT u.id    unit,
                          u.area  area,
                          c.price contract_value,
                          c.id    contract_id
                   FROM   unit u
                          INNER JOIN contractunit cu
                                  ON u.id = cu.unit_id
                          INNER JOIN contract c
                                  ON c.id = cu.contract_id) b
               ON a.contract_id = b.contract_id;