Revision history for QueriesForCandidates
Revision [12142]
Last edited on 2014-12-11 15:29:31 by KatherineMatthews [fixed query and explanation]Additions:
**Number of candidates running for state legislature and statewide offices**
ORDER BY ec.state_id ;
ORDER BY ec.state_id ;
Deletions:
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
WHERE e.officetype_id = 'L' AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8' GROUP BY ec.state_id
ORDER BY number_running ;
****
ORDER BY ec.state_id
****
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
GROUP BY ec.state_id
ORDER BY number_running
Additions:
%%
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE e.officetype_id = 'L' AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8' GROUP BY ec.state_id
ORDER BY number_running ;
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id, e.officetype_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE (e.officetype_id = 'L' OR e.officetype_id = 'S') AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8'
GROUP BY ec.state_id, e.officetype_id
ORDER BY ec.state_id
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE (e.officetype_id = 'L' OR e.officetype_id = 'S') AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8'
GROUP BY ec.state_id
ORDER BY number_running
%%
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE e.officetype_id = 'L' AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8' GROUP BY ec.state_id
ORDER BY number_running ;
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id, e.officetype_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE (e.officetype_id = 'L' OR e.officetype_id = 'S') AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8'
GROUP BY ec.state_id, e.officetype_id
ORDER BY ec.state_id
%%
****
%%
SELECT COUNT (DISTINCT candidate_id) number_running, ec.state_id
FROM office o JOIN election_candidate ec USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN election e USING (election_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
WHERE (e.officetype_id = 'L' OR e.officetype_id = 'S') AND e.electionyear = '2014' AND esc.electioncandidatestatus_id = '8'
GROUP BY ec.state_id
ORDER BY number_running
%%
Additions:
**The following query returns a list of all candidates, along with offices and districts they are running for, and NIMSP id's.**
Deletions:
Additions:
SELECT ecand.match_key, ecand.candidate_id, finsrc.code nimsp_id, ecand.lastname, ecand.firstname, ecand.middlename, ecand.nickname, ecand.suffix, ecand.state_id, ecand.officetype_id, ecand.office_id, ecand.office, ecand.district
FROM
(SELECT UPPER(e.electionyear ""||"" c.lastname ""||"" LEFT(c.firstname, 1) ""||"" ec.state_id ""||"" o.name ""||"" dn.name) match_key, e.electionyear, ec.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, ec.state_id, e.officetype_id, ec.office_id, o.name office, ec.districtname_id, dn.name district
JOIN election_candidate ec USING (election_id)
JOIN office o USING (office_id)
JOIN candidate c USING (candidate_id)
LEFT JOIN districtname dn USING (districtname_id)
LEFT JOIN
(SELECT *
FROM finsource_candidate fsc
JOIN finsource USING (finsource_id)
USING (candidate_id) ;
FROM
(SELECT UPPER(e.electionyear ""||"" c.lastname ""||"" LEFT(c.firstname, 1) ""||"" ec.state_id ""||"" o.name ""||"" dn.name) match_key, e.electionyear, ec.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, ec.state_id, e.officetype_id, ec.office_id, o.name office, ec.districtname_id, dn.name district
JOIN election_candidate ec USING (election_id)
JOIN office o USING (office_id)
JOIN candidate c USING (candidate_id)
LEFT JOIN districtname dn USING (districtname_id)
LEFT JOIN
(SELECT *
FROM finsource_candidate fsc
JOIN finsource USING (finsource_id)
USING (candidate_id) ;
Deletions:
FROM
(SELECT UPPER(e.electionyear ""||"" c.lastname ""||"" LEFT(c.firstname, 1) ""||"" ec.state_id ""||"" o.name ""||"" dn.name) match_key, e.electionyear, ec.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, ec.state_id, e.officetype_id, ec.office_id, o.name office, ec.districtname_id, dn.name district
JOIN election_candidate ec USING (election_id)
JOIN office o USING (office_id)
JOIN candidate c USING (candidate_id)
LEFT JOIN districtname dn USING (districtname_id)
LEFT JOIN
(SELECT * FROM finsource_candidate fsc JOIN finsource USING (finsource_id)
USING (candidate_id) ;
