sql-for-ire-crime-data.sql

SELECT ORI, AGENCY, MAX(CENPOP1 + CENPOP2 + CENPOP3)  as population, sum(MRDR1) as murders, 
   sum(MANSLT1) as manslaughters, sum(ROBT1) as robberies, sum(ASLTT1) as assaults, 
   sum(ASSMPL1) as simple_assaults, sum(BURGT1) as burglaries, sum(LARCT1) as larcenies, 
   sum(VHTHFTT1) as motor_vehicle_theft, sum(ALLFTOT1) as all_crimes
FROM  RETA14_AZ
GROUP BY 1, 2
ORDER BY 3 DESC;

CREATE TABLE az_crime2014 AS
SELECT ORI, AGENCY, ADDRS2, MAX(CENPOP1 + CENPOP2 + CENPOP3) as population14, sum(MRDR1) as murders14, 
   sum(MANSLT1) as manslaughters14, sum(ROBT1) as robberies14, sum(ASLTT1) as assaults14, 
   sum(ASSMPL1) as simple_assaults14, sum(BURGT1) as burglaries14, sum(LARCT1) as larcenies14, 
   sum(VHTHFTT1) as motor_vehicle_theft14, sum(ALLFTOT1) as all_crimes14
FROM  RETA14_AZ
GROUP BY 1, 2
ORDER BY 3 DESC;

CREATE TABLE az_crime2013 AS
SELECT ORI, AGENCY, ADDRS2, MAX(CENPOP1 + CENPOP2 + CENPOP3) as population13, sum(MRDR1) as murders13, 
   sum(MANSLT1) as manslaughters13, sum(ROBT1) as robberies13, sum(ASLTT1) as assaults13, 
   sum(ASSMPL1) as simple_assaults13, sum(BURGT1) as burglaries13, sum(LARCT1) as larcenies13, 
   sum(VHTHFTT1) as motor_vehicle_theft13, sum(ALLFTOT1) as all_crimes13
FROM  RETA13_AZ
GROUP BY 1, 2
ORDER BY 3 DESC;

SELECT * FROM az_crime2013 INNER JOIN az_crime2014 USING (ORI, AGENCY, ADDRS2)
WHERE population14 > 0 AND population13 > 0;

SELECT * FROM az_crime2013 INNER JOIN az_crime2014 USING (ORI, AGENCY, ADDRS2)
WHERE population14 = 0 OR population13 = 0;

CREATE TABLE export_crime AS
SELECT * FROM az_crime2013 INNER JOIN az_crime2014 USING (ORI, AGENCY, ADDRS2)
WHERE population14 > 0 AND population13 > 0;