**The following query returns a list of all candidates, along with offices and districts they are running for, and NIMSP id's.**

%%
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
FROM election e
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)
WHERE e.electionyear < '2013' AND e.officetype_id IN ('L', 'K', 'S')
ORDER BY e.electionyear DESC, ec.state_id, o.name DESC, ec.districtname_id) ecand
LEFT JOIN
(SELECT *
FROM finsource_candidate fsc
JOIN finsource USING (finsource_id)
WHERE fsc.finsource_id = 3) finsrc
USING (candidate_id) ;
%%

**Number of candidates running for state legislature and statewide offices**

%%
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 ;
%%