Brijesh Shah Brijesh Shah - 4 months ago 8
SQL Question

select multiple column with different condition

User table with following fields.

id
agent_id
locality
total
building_type
price


I am getting different stats result with following queries

SELECT agent_id, COUNT(*) AS stat_1
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 0 AND 200000)
GROUP BY "agent_id"
order by agent_id

SELECT agent_id, COUNT(*) AS stat_2
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 200001 AND 350000)
GROUP BY "agent_id"
order by agent_id

SELECT agent_id, COUNT(*) AS stat_3
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 3500001 AND 500000)
GROUP BY "agent_id"
order by agent_id


But I want to get result in same query for all stats like

SELECT agent_id,
COUNT(*) AS stat_1,
COUNT(*) AS stat_2,
COUNT(*) AS stat_3
from users
where <Conditions>


How I can get this result in one query?

Answer

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_id
order by agent_id