Postgres 9.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 9.3.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


create table master_object (
  id int,
  name varchar(20)
);

insert into master_object values (1, 'mark'), (2, 'zugen');

create table records_indikator (
  object_id int,
  is_active int,
  val int,
  indicator_id int
);

insert into records_indikator values
(1, 0, 0, 1),
(1, 1, 0, 2),
(1, 1, 1, 3),
(2, 1, 1, 1),
(3, 0, 1, 2),
(4, 1, 1, 3);

INSERT 0 6


select r.object_id, sum(case when is_active=1 then 1 else 0 end) as total_actives, count(*) as total_records
from master_object m
inner join records_indikator r on r.object_id = m.id
group by r.object_id


object_id
total_actives
total_records


1
2
3

2
1
1



SELECT 2


select sum(case when total_actives = total_records then 1 else 0 end)::float/ count(*) as percentage
from (
  select r.object_id, sum(case when is_active=1 then 1 else 0 end) as total_actives, count(*) as total_records
  from master_object m
  inner join records_indikator r on r.object_id = m.id
  group by r.object_id
) as s


percentage


0.5



SELECT 1