CODEWITHSUNDEEP
sqlpostgresqlcount
tapzx2
tapzx2

Reputation: 1071

Window Query for Counting Subsections

I'm trying to count the number of jobs in an area AND count the number of each type of job in that area.

I think the solution is a window query and I've read a good number of window query questions and I still can't figure it out. I don't understand how I should compose the query.

My functioning query to count the number of jobs in an area is:

SELECT region.id, count(*) as total_jobs
FROM region_center INNER JOIN region 
ON ST_DWithin(region_center.geom, region.geom, 2640)
GROUP BY region.id;

The idea, I think, is to do a query and then select from that query like so:

WITH T1 AS (
SELECT region.id, region.job
FROM region_center INNER JOIN region 
ON ST_DWithin(region_center.geom, region.geom, 2640)
)
 SELECT
  (SELECT id FROM T1 GROUP BY id),
  (SELECT count(*) FROM T1 GROUP BY id) as Total,
  (SELECT count(*) from T1 where job = 'Janitor' GROUP BY id) as Janitor
  (SELECT count(*) from T1 where job = 'Repair' GROUP BY id) as Repair
;

But this obviously doesn't work. Any suggestions?

Upvotes: 0

Views: 41

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

select
    region.id,
    count(*) as total_jobs,
    count(job = 'Janitor' or null) as "Janitor"
    count(job = 'Repair' or null) as "Repair"
from
    region_center
    inner join
    region on st_dwithin(region_center.geom, region.geom, 2640)
group by region.id;

Upvotes: 1

Related Questions