Window Query for Counting Subsections

Question

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?

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2014-05-09T17:57:45

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;

Window Query for Counting Subsections

1 Answers