Data Counts
Below, you will find various queries for finding the amount of data we need to collect based on numbers from the past.Average Number of Speeches Collected per Day
| SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C FROM (SELECT s.speechdate, COUNT (s.speech_id) FROM speech s WHERE s.speechdate BETWEEN '2014-09-01' AND '2014-09-30' GROUP BY s.speechdate) a, (SELECT s.speechdate, COUNT (s.speech_id) FROM speech s WHERE s.speechdate BETWEEN '2014-10-01' AND '2014-10-31' GROUP BY s.speechdate) b, (SELECT s.speechdate, COUNT (s.speech_id) FROM speech s WHERE s.speechdate BETWEEN '2014-11-01' AND '2014-11-30' GROUP BY s.speechdate) c |
The Number of Issues Positions Collected by Date
| SELECT COUNT (speech_id), created FROM speech WHERE speechtype_id = '7' AND created BETWEEN '2013-01-28' AND '2013-2-01' GROUP BY created ORDER BY created desc |
The Number of Federal and State Offices
| SELECT COUNT (oc.candidate_id), ot.name, o.name FROM office o JOIN office_candidate oc USING (office_id) JOIN officetype ot USING (officetype_id) WHERE o.officetype_id IN ('C', 'G', 'P', 'J', 'K', 'L') AND oc.officecandidatestatus_id = '1' GROUP BY ot.name, o.name ORDER BY ot.name, o.name |
The Number of Candidates for Office by Election
| SELECT COUNT (ec.candidate_id), e.electionyear, e.officetype_id FROM election e JOIN election_candidate ec USING (election_id) WHERE e.officetype_id IN ('C', 'G', 'P', 'J', 'K', 'L') AND e.electionyear BETWEEN '2000' AND '2015' GROUP BY e.electionyear, e.officetype_id ORDER BY e.electionyear DESC, e.officetype_id |
The Number of Endorsements Collected by Election Year
| SELECT COUNT (ec.endorse_candidate_id), ot.name, el.electionyear FROM endorse_candidate ec JOIN endorse en USING (endorse_id) JOIN sig s USING (sig_id) JOIN election el USING (election_ID) JOIN officetype ot USING (officetype_id) WHERE s.state_id = 'NA' GROUP BY ot.name, el.electionyear ORDER BY el.electionyear DESC, ot.name |
A List of Ratings Collected by Election Year
| SELECT r.rating_id, r.sig_id, r.span, s.state_id FROM sig s JOIN rating r using (sig_id) WHERE r.span IN ('2014', '2013', '2013-2014') AND r.release_id = '2' AND s.state_id != 'NA' ORDER BY s.state_id, span |
Ballot Measures by Election Year
| SELECT COUNT (measure_id), electiondate from measure group by electiondate order by electiondate desc |
The Total Number of Localities
| SELECT COUNT (local_id), localtype_id FROM local GROUP BY localtype_id |
The Total Number of Active City and County Officials
| SELECT COUNT (oc.candidate_id), ot.name FROM office o JOIN office_candidate oc USING (office_id) JOIN officetype ot USING (officetype_id) WHERE o.officetype_id IN ('H', 'N', 'M') AND oc.officecandidatestatus_id = '1' GROUP BY ot.name |
