abq_sql_tableflip

flip.sql

SELECT
zip_codes,
SUM (case WHEN type = 'jobs' THEN cnt ELSE 0 END) AS jobs,
SUM (case WHEN type = 'education' THEN cnt ELSE 0 END) AS education,
SUM (case WHEN type = 'housing' THEN cnt ELSE 0 END) AS housing,
SUM (case WHEN type = 'childcare' THEN cnt ELSE 0 END) AS childcare,
SUM (case WHEN type = 'transportation' THEN cnt ELSE 0 END) AS transportation,
SUM (case WHEN type = 'health' THEN cnt ELSE 0 END) AS health,
SUM (case WHEN type = 'grants' THEN cnt ELSE 0 END) AS grants,
SUM (case WHEN type = 'food' THEN cnt ELSE 0 END) AS food

FROM
(
  SELECT x.zip_codes, x.type, COUNT(*) AS cnt

  FROM abq_nfp_types_1 x JOIN zipcodes_abq y ON x.zip_codes::VARCHAR = y.geoid10

  WHERE zip_codes::VARCHAR = geoid10 

  group by 1, 2

) z

GROUP BY 1