My Query:
select c.id, sum(case when r.paid_out>='1' then 0 else 1 end) as all_paids
from people p1, houses h, policies p2, receipts r
where p1.id = h.id
and h.code = p2.code
and p2.code_policy = r.code_policy
group by p1.id
having all_paids= 0;
Postesql return me the following error:
ERROR : There is no column 'all_paids'
I have tried several things but nothing, any help is appreciated
First, learn to use proper join syntax.
Second, you can use the expression for the having clause or a subquery:
select p1.id
from people p1 join
houses h
on p1.id = h.id join
policies p2
on h.code = p2.code join
receipts r
on p2.code_policy = r.code_policy
group by p1.id
having sum(case when r.paid_out >= '1' then 0 else 1 end) = 0;
Note: there is no c.id defined, so I assume the select should be p1.id based on the group by.
I should say that this logic would often be expressed using not exists:
select p1.*
from people p1
where not exists (select 1
from houses h
policies p2
on h.code = p2.code join
receipts r
on p2.code_policy = r.code_policy
where p1.id = h.id and
r.paid_out >= '1'
);
This eliminates the aggregation.
