counts on pg

gistfile1.txt

Run counts on production database,
  group by districts, fips (state? or county?)

        ## Political Info
        'party'
        'registered_at'
        'is_active_voter'
        'is_perm_absentee'

        ## District Info


districts = w%{ state_file_cd state_file_sd state_file_hd county_district judicial_district city_district township village_district fire_district school_district school_sub_district ward nbec_precinct_code precinct }


## In Rails

results = []

districts = %w{ state_file_cd state_file_sd state_file_hd county_district judicial_district city_district township village_district fire_district school_district school_sub_district ward nbec_precinct_code precinct }

def query(type)
  "is_dropped_from_file = false and #{type} is not null"
end

US_STATES.each do |state|
  counts = {
    'state' => state,
    'total' => MasterVoter.on(state).where(is_dropped_from_file: false).count
  }

  districts.each do |type|
    counts[type] = MasterVoter.on(state).where(query(type)).count
  end

  results << counts
end

File.open('/home/vyki/counts_us_districts', 'w') do |file|
  results.each { |r| file.puts r.values.join(',') }
end


File.open('errors', 'w') do |file|
  File.readlines('test_file').each do |row|
    begin
       CSV.parse(row)
    rescue CSV::MalformedCSVError
       file << row
    end
  end
end
## On Postgres

results.each { |r| puts r.keys.join(',') }

select
  fips
  , count(*) as total
  , sum(case when state_file_cd is not null then 1 else 0 end) as state_file_cd
  , sum(case when state_file_sd is not null then 1 else 0 end) as state_file_sd
  , sum(case when state_file_hd is not null then 1 else 0 end) as state_file_hd
  , sum(case when school_district is not null then 1 else 0 end) as school_district
  , sum(case when fire_district is not null then 1 else 0 end) as fire_district
from nd_voters
where is_dropped_from_file = false 
group by fips;


postgres (3)

district name | state | null count | not null count
---------------------------------------------------
state_file_cd | FL    |            |
state_file_sd | FL    |            |
state_file_hd | FL    |            |



pivot


district_name | AL   | FL
---------------------------
state_file_cd | 100% | 99%

COUNT(*) FROM al_voters