query

SELECT "agents"."id",
       "agents"."office_id",
       "agents"."building_type_id",
       "agents"."office",
       "agents"."duration_qouta",
       "agents"."pic_name",
       "agents"."image",
       "agents"."phone",
       "agents"."email",
       "agents"."city",
       "agents"."province",
       "agents"."district",
       "agents"."is_priority",
       "agents"."desc",
       "agents"."type_agent",
       "agents"."order_quota",
       "agents"."user_id",
       "agents"."is_active",
       "agents"."is_trusted",
       "agents"."is_spesialis",
       "agents"."is_gold_seller",
       "agents"."reason",
       "agents"."link",
       "agents"."twitter",
       "agents"."ig",
       "agents"."yt",
       "agents"."fb",
       "agents"."createdAt",
       "agents"."updatedAt",
       (SELECT COUNT(*)
        FROM advertises
        where advertises.agent_id = agents.id and advertises.is_publish = 1)                                   AS "tot_listing",
       (SELECT COUNT(*)
        FROM advertises
        where advertises.agent_id = agents.id
          and advertises.is_publish = 4
          and advertises.listing_type = 1)                                                                     AS "tot_sell",
       (SELECT COUNT(*)
        FROM advertises
        where advertises.agent_id = agents.id
          and advertises.is_publish = 4
          and advertises.listing_type = 2)                                                                     AS "tot_rent",
       (SELECT min(price)
        FROM advertises
        where advertises.agent_id = agents.id
          and advertises.is_publish = 1)                                                                       AS "price_min",
       (SELECT max(price)
        FROM advertises
        where advertises.agent_id = agents.id
          and advertises.is_publish = 1)                                                                       AS "price_end",
       (SELECT avg(price)
        FROM advertises
        where advertises.agent_id = agents.id
          and advertises.is_publish = 1)                                                                       AS "price_avg"
FROM "agents" AS "agents"
         LEFT OUTER JOIN "advertises" AS "advertises" ON "agents"."id" = "advertises"."agent_id"
WHERE ("agents"."is_active" = 2 AND "agents"."type_agent" != 4)
GROUP BY "agents"."id"