SQL to query active vs. inactive voters, primary vs. general election participation

Postgres SQL code to query a Whatcom County Voter database to comapare previous and current elections for active vs. inactive voters, precincts vs. general election participation via ballotcounted and precinctid fields. Political piece is here.

/* Postgres SQL code to query a Whatcom County Voter database to comapare previous and current elections for active vs. inactive voters, precincts vs. general election participation via ballotcounted and precinctid fields. 4 Sections. Section number four produces output like this:

ERIC2014_05_21_2014=# Select *  from JoinStats where precinctid IN (Select precincts42 from LD42) Order by GEDiff;

 precinctid | primary | ge2013 | ge2012 | nprimary | nge2013 | nge2012 | naprimary | nage2013 | nage2012 | pdiff | gediff | ngediff
------------+---------+--------+--------+----------+---------+---------+-----------+----------+----------+-------+--------+---------
        201 |     441 |    559 |    899 |      790 |     569 |     147 |        41 |      144 |      226 |   118 |   -340 |     422
        133 |     209 |    259 |    557 |      765 |     688 |     346 |        16 |       43 |       87 |    50 |   -298 |     342
        229 |     205 |    402 |    671 |      804 |     538 |     191 |        32 |      101 |      179 |   197 |   -269 |     347
        137 |     211 |    286 |    544 |      736 |     629 |     335 |        20 |       52 |       88 |    75 |   -258 |     294
        101 |     290 |    421 |    679 |      634 |     484 |     182 |        14 |       33 |       77 |   131 |   -258 |     302
....
*/

-- #1 Active and Inactive
drop table JoinAI;
drop table Active;
drop table Inactive;

create table Active as Select precinctid,Count(statuscode) as Active from voterdb091014 where Statuscode = 'A' group by precinctid,Statuscode order by PrecinctID;

create table Inactive as Select precinctid,Count(statuscode) as Inactive from voterdb091014 where Statuscode = 'I' group by precinctid,Statuscode order by PrecinctID;

create table JoinAI as Select Active.precinctid,Active,Inactive from Active,Inactive  where Active.precinctid =  Inactive.precinctid Order By precinctid DESC;

Select *,(Active + Inactive) as All,(Active/((Active + Inactive)::Float)* 100)::INT as PCTActive from JoinAI Order by PCTActive;

Select *,(Active + Inactive) as All,(Active/((Active + Inactive)::Float)* 100)::INT as PCTActive from JoinAI where precinctid IN (Select precincts42 from LD42) Order by Active DESC;

-- #2 Active, Inactive plus 2014 Primary
drop table JoinAll;
drop table JoinAI;
drop table JoinNV;
drop table Active;
drop table Inactive;
drop table Voted;
drop table NotVoted;

create table Active as Select precinctid,Count(statuscode) as Active from voterdb091014 where Statuscode = 'A' group by precinctid,Statuscode order by PrecinctID;

create table Inactive as Select precinctid,Count(statuscode) as Inactive from voterdb091014 where Statuscode = 'I'group by precinctid,Statuscode order by PrecinctID;

create table Voted as Select precinctid,Count(ballotcounted_1) as Voted from voterdb091014 where ballotcounted_1 = '1'group by precinctid,Ballotcounted_1 order by PrecinctID;

create table NotVoted as Select precinctid,Count(ballotcounted_1) as NotVoted from voterdb091014 where ballotcounted_1 = '0' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table JoinAI as Select Active.precinctid,Active,Inactive from Active,Inactive 
  where Active.precinctid =  Inactive.precinctid Order By precinctid DESC;

create table JoinNV as Select Voted.precinctid,Voted,NotVoted from Voted,NotVoted 
  where Voted.precinctid =  NotVoted.precinctid Order By precinctid DESC;

create table JoinAll as Select JoinAI.precinctid,Active,Inactive,Voted,NotVoted from JoinAI,JoinNV  where JoinAI.precinctid =  JoinNV.precinctid Order By precinctid DESC;

Select *,(Active + Inactive) as AllStatus,
(Active/((Active + Inactive)::Float)* 100)::INT as PCTActiveNOW,
(Voted/((Voted + NotVoted)::Float)* 100)::INT as PCTVotedinPrimary
from JoinAll Order by Active DESC;

Select *,(Active + Inactive) as AllStatus,
(Active/((Active + Inactive)::Float)* 100)::INT as PCTActiveNOW,
(Voted/((Voted + NotVoted)::Float)* 100)::INT as PCTVotedinPrimary
from JoinAll 
where JoinAll.precinctid IN (Select precincts42 from LD42)
Order by PCTVotedinPrimary;

--#3 2014 Primary, GE 2013, GE 2012
drop table voterdbcur;
drop table Join145;
drop table Join1;
drop table Join4;
drop table Join5;
drop table Voted1;
drop table NotVoted1;
drop table Voted4;
drop table NotVoted4;
drop table Voted5;
drop table NotVoted5;

create table voterdbcur as (Select * from voterdb091014 where Statuscode = 'A');

create table Voted1 as Select precinctid,Count(ballotcounted_1) as Voted from voterdbcur where ballotcounted_1 = '1' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table NotVoted1 as Select precinctid,Count(ballotcounted_1) as NotVoted from voterdbcur where ballotcounted_1 = '0' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table Voted4 as Select precinctid,Count(ballotcounted_4) as Voted from voterdbcur where ballotcounted_4 = '1' 
group by precinctid,Ballotcounted_4 order by PrecinctID;
create table NotVoted4 as Select precinctid,Count(ballotcounted_4) as NotVoted from voterdbcur where ballotcounted_4 = '0'
group by precinctid,Ballotcounted_4 order by PrecinctID;

create table Voted5 as Select precinctid,Count(ballotcounted_5) as Voted from voterdbcur where ballotcounted_5 = '1' group by precinctid,Ballotcounted_5 order by PrecinctID;

create table NotVoted5 as Select precinctid,Count(ballotcounted_5) as NotVoted from voterdbcur where ballotcounted_5 = '0'group by precinctid,Ballotcounted_5 order by PrecinctID;

create table Join1 as Select Voted1.precinctid,Voted,NotVoted from Voted1,NotVoted1 
  where Voted1.precinctid =  NotVoted1.precinctid Order By precinctid DESC;

create table Join4 as Select Voted4.precinctid,Voted,NotVoted from Voted4,NotVoted4 
  where Voted4.precinctid =  NotVoted4.precinctid Order By precinctid DESC;

create table Join5 as Select Voted5.precinctid,Voted,NotVoted from Voted5,NotVoted5
  where Voted5.precinctid =  NotVoted5.precinctid Order By precinctid DESC;

create table Join145 as Select Join1.precinctid,Join1.Voted as Primary,Join4.Voted as GE2013, Join5.Voted as GE2012, Join1.NotVoted as nPrimary, Join4.NotVoted as nGE2013,Join5.NotVoted as nGE2012 from Join1,Join4,Join5 where Join1.precinctid =  Join4.precinctid  and  Join1.precinctid =  Join5.precinctidaa Order By precinctid DESC;

drop table JoinStats;
-- Select * from Join145 Order by PrecinctID;
-- Select *,(GE2013 - Join145.primary) as pDiff,(GE2013 - GE2012) as GEDiff, (nGE2013 - nGE2012)as nGEDiff from Join145 Order by PrecinctID;

create table JoinStats as (Select *,(GE2013 - Join145.primary) as pDiff,(GE2013 - GE2012) as GEDiff, (nGE2013 - nGE2012)as nGEDiff from Join145 Order by PrecinctID);
Select *  from JoinStats where precinctid IN (Select precincts42 from LD42) Order by GEDiff;

--#4 As three but includes BallotCounted ='' or null.
drop table voterdbcur;
drop table Join145;
drop table Join1;
drop table Join4;
drop table Join5;
drop table Voted1;
drop table NotVoted1;
drop table NA1;
drop table Voted4;
drop table NotVoted4;
drop table NA4;
drop table Voted5;
drop table NotVoted5;
drop table NA5;

create table voterdbcur as (Select * from voterdb091014 where Statuscode = 'A');

create table Voted1 as Select precinctid,Count(ballotcounted_1) as Voted from voterdbcur where ballotcounted_1 = '1' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table NotVoted1 as Select precinctid,Count(ballotcounted_1) as NotVoted from voterdbcur where ballotcounted_1 = '0' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table NA1 as Select precinctid,Count(ballotcounted_1) as NA from voterdbcur where ballotcounted_1 = '' group by precinctid,Ballotcounted_1 order by PrecinctID;

create table Voted4 as Select precinctid,Count(ballotcounted_4) as Voted from voterdbcur where ballotcounted_4 = '1' group by precinctid,Ballotcounted_4 order by PrecinctID;

create table NotVoted4 as Select precinctid,Count(ballotcounted_4) as NotVoted from voterdbcur where ballotcounted_4 = '0'group by precinctid,Ballotcounted_4 order by PrecinctID;

create table NA4 as Select precinctid,Count(ballotcounted_4) as NA from voterdbcur where ballotcounted_4 = '' group by precinctid,Ballotcounted_4 order by PrecinctID;

create table Voted5 as Select precinctid,Count(ballotcounted_5) as Voted from voterdbcur where ballotcounted_5 = '1' group by precinctid,Ballotcounted_5 order by PrecinctID;

create table NotVoted5 as Select precinctid,Count(ballotcounted_5) as NotVoted from voterdbcur where ballotcounted_5 = '0' group by precinctid,Ballotcounted_5 order by PrecinctID;

create table NA5 as Select precinctid,Count(ballotcounted_5) as NA from voterdbcur where ballotcounted_5 = '' group by precinctid,Ballotcounted_5 order by PrecinctID;

create table Join1 as
(Select 
Voted1.precinctid,
Voted,NotVoted,NA
from Voted1,NotVoted1,NA1
  where 
Voted1.precinctid =  NotVoted1.precinctid 
and
Voted1.precinctid = NA1.precinctid
Order By precinctid DESC);

create table Join4 as 
(Select
Voted4.precinctid,
Voted,NotVoted,NA
from Voted4,NotVoted4,NA4
  where
Voted4.precinctid =  NotVoted4.precinctid
and
Voted4.precinctid =  NA4.precinctid
Order By precinctid DESC);

create table Join5 as 
(Select
Voted5.precinctid,
Voted,NotVoted,NA
from Voted5,NotVoted5,NA5
  where
Voted5.precinctid =  NotVoted5.precinctid
and
Voted5.precinctid =  NA5.precinctid
Order By precinctid DESC);

create table Join145 as 
(Select Join1.precinctid,
Join1.Voted as primary,
Join4.Voted as GE2013,
Join5.Voted as GE2012,
Join1.NotVoted as nPrimary,
Join4.NotVoted as nGE2013,
Join5.NotVoted as nGE2012,
Join1.NA as naPrimary,
Join4.NA as naGE2013,
Join5.NA as naGE2012
from Join1,Join4,Join5 
where Join1.precinctid =  Join4.precinctid  
and  
Join1.precinctid =  Join5.precinctid
Order By precinctid DESC);

drop table JoinStats;
-- Select * from Join145 Order by PrecinctID;
-- Select *,(GE2013 - Join145.primary) as pDiff,(GE2013 - GE2012) as GEDiff, (nGE2013 - nGE2012)as nGEDiff from Join145 Order by PrecinctID;

create table JoinStats as 
(Select *,
(GE2013 - Join145.primary) as pDiff,
(GE2013 - GE2012) as GEDiff,
(nGE2013 - nGE2012) as nGEDiff
from Join145 Order by PrecinctID);


-- BallotCounted = ''
Select *  from JoinStats where precinctid IN (Select precincts42 from LD42) Order by GEDiff;

/* 
ERIC2014_05_21_2014=# Select SUM(naprimary) as NAPrimary, SUM(nage2013) as NA2013 , SUM(nage2012) as NA2012 from Joinstats;
 naprimary | na2013 | na2012
-----------+--------+--------
      1840 |   6903 |  14172
(1 row)
*/

ERIC2014_05_21_2014=# Select SUM(naprimary) as NAPrimary, SUM(nage2013) as NA2013 , SUM(nage2012) as NA2012 from Joinstats where precinctid in (Select precincts42 from ld42);

/*  naprimary | na2013 | na2012
-----------+--------+--------
      1189 |   4538 |   8335
(1 row)
*/