Untitled

SELECT
CASE
    WHEN b.id IS NULL
    THEN (SELECT id FROM buildings WHERE buildings.street = v.street AND buildings.building_no = v.building_no LIMIT 1)
    ELSE b.id
END as id,
v.countryid,
c.name as country_name,
v.city,
d.name as district_name,
v.street,
v.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,
v.street|| ' '|| v.building_no||', '||v.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(
    SELECT
    customer_addresses.customer_id AS customerid,
    a.flat AS apartment,
    a.country_id AS countryid,
    a.city,
    a.street,
    a.house AS building_no,
    cc.name AS country
    FROM customer_addresses customer_addresses
    JOIN customers c ON c.id = customer_addresses.customer_id
    JOIN addresses a ON a.id = customer_addresses.address_id
    LEFT JOIN countries cc ON a.country_id = cc.id
    WHERE c.deleted = 0
    AND a.street='Šv. Ignoto g.'
    AND a.house='3'
    ) AS v
LEFT JOIN buildings b 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 countries c on (c.id=v.countryid)
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)
GROUP BY
b.id,
    v.countryid,
    v.street,
    v.building_no,
    v.city,
    c.name,
    d.name,
    b.apartments_no,
    b.description,
    vs.name,
    cs.name,
    co.name,
    bp.id,
    bp.number,
    bp.completion_act_date