select multiple column with different condition

Postgres 9.4 solves these kind of problems in an elegant way by adding a filter clause that allows you to apply a condition on aggregate functions:

SELECT   agent_id,          COUNT(*) FILTER (WHERE "price" BETWEEN 0 AND 200000) AS stat_1,         COUNT(*) FILTER (WHERE "price" BETWEEN 200001 AND 350000) AS stat_2,         COUNT(*) FILTER (WHERE "price" BETWEEN 3500001 AND 500000) AS stat_3FROM     users WHERE    "building_type" = 'single' GROUP BY "agent_id" ORDER BY "agent_id"

With earlier versions that don't allow a filter clause, you could implement the same behavior yourself by applying the aggregate function to a case expression. Here, we utilize count's property of ignoring nulls:

SELECT   agent_id,          COUNT(CASE WHEN "price" BETWEEN 0 AND 200000 THEN 1 END) AS stat_1,         COUNT(CASE WHEN "price" BETWEEN 200001 AND 350000 THEN 1 END) AS stat_2,         COUNT(CASE WHEN "price" BETWEEN 3500001 AND 500000 THEN 1 END) AS stat_3FROM     users WHERE    "building_type" = 'single' GROUP BY "agent_id" ORDER BY "agent_id"

You can use conditional aggregation:

SELECT agent_id,        COUNT(*) filter (where price BETWEEN 0 AND 200000) as stat_1,       COUNT(*) filter (where price BETWEEN 200001 AND 350000)  AS stat_2,        COUNT(*) filter (where price BETWEEN 3500001 AND 500000)  AS stat_3 from users WHERE building_type = 'single' GROUP BY agent_idorder by agent_id