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