zipcode research

zipcode_research.sql

 select a.fips, b.zipcode, count_n, cities, p_50_sqft, p_50_estval
 from (
   select fips, zipcode, cast(sum(n) as unsigned) count_n -- cast to int, otherwise decimal
   from feature_fips_zips_counts
   where fips = '48453'
   and sfr=1
   group by 1,2
 ) a
   left join feature_tax_zipcode_city b
   on a.zipcode=b.zipcode
   left join (
   select zipcode, p_50 p_50_sqft
   from feature_aggregate_tax_zipcode z
   where z.feature='square_footage'
 ) y
 on a.zipcode=y.zipcode
 left join (
   select zipcode, p_50 p_50_estval
   from feature_aggregate_tax_zipcode z
   where z.feature='estimated_value'
 ) z
 on a.zipcode=z.zipcode
 where a.fips = '48453'
   and b.zipcode is not null
 -- and sfr=1
 group by 1, 2;