Untitled

SELECT
b.id,
b.countryid,
c.name as country_name,
b.city,
d.name as district_name,
b.street,
b.building_no,
b.apartments_no,
b.description,
count(distinct v.customerid) as connected_cust,
string_agg(v.customerid::text, ',') as customer,
count(distinct CASE when active_liability(v.customerid) > 0 then v.customerid else null end) as connected_act_cust,
trim(leading '0' from max(substring('0000000000'||v.apartment from '.........$'))) max_apt,
round(100.0 * count(
distinct CASE when active_liability(v.customerid) > 0 then v.customerid else null end)
/ CASE when b.apartments_no != 0 then b.apartments_no else 1 end,
2) as connection_pct,
b.street|| ' '|| b.building_no||', '||b.city||', '|| c.name as bname,
vs.name as building_type,
cs.name as connection_status,
b.connection_cost,
b.month_cost,
b.created_date_time,
b.connected_date_time,
b.modified_date_time,
b.created_by,
b.changed_by,
b.community_id,
b.cable_no,
co.name as community,
bp.id as project_id,
bp.number as project_number,
bp.completion_act_date
from
buildings b
left join countries   c on (c.id=b.countryid)
left join addresses_v v on (coalesce(b.countryid, 1) = coalesce(v.countryid, 1) and b.city = v.city and b.street = v.street and b.building_no = v.building_no)
left join fl_districts d on (b.district_id = d.id)
left join value_sets vs on (vs.code = b.building_type and vs.type = 'building_type')
left join value_sets cs on (cs.code = b.connection_status::text and cs.type = 'bld_connection_status')
left join fl_buildings_communities co on (b.community_id = co.id)
left join fl_buildings_projects bp on (b.project_id = bp.id)
where
b.id = 5113
group by
b.id,
b.countryid,
c.name,
b.city,
d.name,
b.street,
b.building_no,
b.apartments_no,
b.description,
vs.name,
cs.name,
co.name,
bp.id,
bp.number,
bp.completion_act_date
order by
b.street,
substring('0000'||b.building_no from '.....$')