'Select Query of third table

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.

inserir a descrição da imagem aqui

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.

inserir a descrição da imagem aqui

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
sqljoinfirebird


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions