@@=====**Useful Research Queries - SQL**=====@@

====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||