select
zips.zipcode,
zips.geom,
count(nyc_311.id) as count
from
nyc_zips zips
join nyc_311 on nyc_311.incident_zip = zips.zipcode
where
nyc_311.complaint_type = 'Illegal Parking'
group by
zips.zipcode,
zips.geom
-- In this CTE, which has an alias of "a",
-- we pull the data we need from the nyc_311 data
-- and filter to just the results that match "Illegal Parking"
with a as (
select
id,
incident_zip as zipcode
from
nyc_311
where
nyc_311.complaint_type = 'Illegal Parking'
)
-- We then join the data from our "temporary table" a to the zipcode data
select
zips.zipcode,
zips.geom,
count(a.id) as count
from
nyc_zips zips
join a using (zipcode)
group by
zips.zipcode,
zips.geom
-- Now we have our entire aggregation in the CTE
with a as (
select
count("unique key") as total,
"incident zip" as zipcode
from
nyc_311
where
nyc_311."complaint type" = 'Illegal Parking'
group by
"incident zip"
)
select
zips.zipcode,
zips.geom,
a.total
from
nyc_zips zips
join a using (zipcode)