otchet

SELECT
    "qq".*, qq.paid * 100 / CASE
WHEN qq.credited = 0 THEN
    1
ELSE
    qq.credited
END AS percent
FROM
    (
        SELECT
            "ld"."id" AS "district_id",
            "ld"."title" AS "district_title",
            "ld"."prefix_short" AS "district_prefix",
            "ll"."title",
            "ll"."prefix_short" AS "city_prefix",
            CASE
        WHEN SUM (ao.credited) IS NULL THEN
            0
        ELSE
            SUM (ao.credited :: NUMERIC)
        END AS credited,
        CASE
    WHEN SUM (ao.paid) IS NULL THEN
        0
    ELSE
        SUM (ao.paid :: NUMERIC)
    END AS paid,
    CASE
WHEN SUM (ro_account.build_area) IS NULL THEN
    0
ELSE
    SUM (ro_account.build_area :: NUMERIC)
END AS build_area
FROM
    "house" AS "hh"
INNER JOIN "home" AS "h" ON h.house_id = hh. ID
INNER JOIN "locality" AS "ld" ON ld. ID = CASE
WHEN hh.district_id IS NULL THEN
    hh.city_id
ELSE
    hh.district_id
END
INNER JOIN "locality" AS "ll" ON ll. ID = hh.locality_id
INNER JOIN "billing_bill_ro_account" AS "ro_account" ON ro_account.home_id = h. ID
INNER JOIN "billing_bill_account_operation" AS "ao" ON ao.billing_bill_ro_account_id = ro_account. ID
AND ao.dt BETWEEN '2014-12-01'
AND '2015-01-31'
WHERE
    (h.hidden = 0)
AND (
    COALESCE (hh.district_id, hh.city_id) = 16870
)
AND (
    OVERLAY (
        CASE
        WHEN hh.street_code IS NULL THEN
            hh.locality_code
        ELSE
            hh.street_code
        END PLACING '00'
        FROM
            12 FOR 6
    ) = '5500000100000'
)
GROUP BY
    "ld"."id",
    "ld"."title",
    "ll"."title",
    "ll"."prefix_short"
    ) AS "qq"