How to get the correct sum of field values ​​in a query with a referencing entry to this cell?

Question

PostgreSQL

The problem is as follows: It is necessary to find the sum of values ​​in the DB field of all cells belonging to certain attributes (i.e. for an array of attributes a cell or a set of cells is created - records in the DB), while there is a 1:M relationship of the "Cells" table and the "Classes" table cells" i.e. knowing the id of the cell, we can make a record several times and, based on this, calculate how many cells in total, how many are occupied and how many are available.

Now the request looks like this:

select 
s.id as Id,
sa.id_attribute as IdAtr,
sum(a.limit) as All,
sum(case when c.status_active = false then 1 else 0 end) as Fact,
sum(a.limit) - sum(case when c.status_active = false then 1 else 0 end) as Access
from public."schedule" as s
left join public."schedule_attribute" as sa on sa.id_schedule = s.id
left join public."access_cell" as a on a.id__attribute = sa.id
left join public."closed_cell" as c on c.id_access_cell = a.id
group by
s.id,
sa.id_attribute

The problem is that if we refer to a free cell once in the closed_cell table, everything is fine, but if several times, then the amount is not calculated correctly, increasing depending on the value in the limit field of the access_cell table.

Answer 1

Sorry for the bad design, I am writing from the phone, here is my example of how to do this (if I understand correctly what you need)

with test as (select id_access, sum(1) as close 
        from Close where status = 0
       group by id_access)
select S.*, Atr.name AS Aname, SUM("limit") AS limit, 
sum( t.close), sum("limit") - sum(t.close)
from Schedule S
join Attribute Atr ON Atr.id_schedule = S.id
join Access Acc on Acc.id_attribute = Atr.id
left join test t on Acc.id = t.id_access
group by Atr.name, S.id, S.name