+
 PRAGMA foreign_keys = ON;
 --
 -- modalidad_states

select state, state_code, modalidad, tipo, subtipo, date, sum(count) as count, sum(population) as population, (sum(count) * 12) / sum(population) * 100000 as rate, "victimas" as type from victimas group by state, state_code, modalidad, tipo, subtipo, date 
UNION ALL 
select state, state_code, modalidad_text as modalidad, tipo_text as tipo, subtipo_text as subtipo, date, count, population, population/count  as rate, "averiguaciones" as type from estados_fuero_comun natural join state_names natural join population_states natural join modalidad_states natural join subtipo_states natural join tipo_states where modalidad_text = "ROBO COMUN" and (tipo_text ="SIN VIOLENCIA" or tipo_text ="CON VIOLENCIA") and subtipo_text = "DE VEHICULOS" and date > '2014-01';
!



