Revision history for ResearchSQLQueries
Additions:
@@=====**Useful Research Queries - SQL**=====@@
Deletions:
Additions:
==The Number of Issues Positions Collected by Date==
||SELECT COUNT (speech_id), created
ORDER BY created desc||
||SELECT COUNT (oc.candidate_id), ot.name, o.name
ORDER BY ot.name, o.name||
||SELECT COUNT (ec.candidate_id), e.electionyear, e.officetype_id
ORDER BY e.electionyear DESC, e.officetype_id||
||SELECT COUNT (ec.endorse_candidate_id), ot.name, el.electionyear
ORDER BY el.electionyear DESC, ot.name||
||SELECT r.rating_id, r.sig_id, r.span, s.state_id
ORDER BY s.state_id, span||
||SELECT COUNT (measure_id), electiondate
order by electiondate desc||
||SELECT COUNT (local_id), localtype_id
GROUP BY localtype_id||
||SELECT COUNT (oc.candidate_id), ot.name
GROUP BY ot.name||
||SELECT COUNT (speech_id), created
ORDER BY created desc||
||SELECT COUNT (oc.candidate_id), ot.name, o.name
ORDER BY ot.name, o.name||
||SELECT COUNT (ec.candidate_id), e.electionyear, e.officetype_id
ORDER BY e.electionyear DESC, e.officetype_id||
||SELECT COUNT (ec.endorse_candidate_id), ot.name, el.electionyear
ORDER BY el.electionyear DESC, ot.name||
||SELECT r.rating_id, r.sig_id, r.span, s.state_id
ORDER BY s.state_id, span||
||SELECT COUNT (measure_id), electiondate
order by electiondate desc||
||SELECT COUNT (local_id), localtype_id
GROUP BY localtype_id||
||SELECT COUNT (oc.candidate_id), ot.name
GROUP BY ot.name||
Deletions:
SELECT COUNT (speech_id), created
ORDER BY created desc<<
<<SELECT COUNT (oc.candidate_id), ot.name, o.name
ORDER BY ot.name, o.name<<
<<SELECT COUNT (ec.candidate_id), e.electionyear, e.officetype_id
ORDER BY e.electionyear DESC, e.officetype_id<<
<<SELECT COUNT (ec.endorse_candidate_id), ot.name, el.electionyear
ORDER BY el.electionyear DESC, ot.name<<
<<SELECT r.rating_id, r.sig_id, r.span, s.state_id
ORDER BY s.state_id, span<<
<<SELECT COUNT (measure_id), electiondate
order by electiondate desc<<
<<SELECT COUNT (local_id), localtype_id
GROUP BY localtype_id<<
<<SELECT COUNT (oc.candidate_id), ot.name
GROUP BY ot.name<<
Additions:
==Average Number of Speeches Collected per Day==
||SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
GROUP BY s.speechdate) c||
||SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
GROUP BY s.speechdate) c||
Deletions:
SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
GROUP BY s.speechdate) c<<
Additions:
<<==The Number of Issues Positions Collected by Date==
SELECT COUNT (speech_id), created
SELECT COUNT (speech_id), created
Deletions:
<<SELECT COUNT (speech_id), created
Additions:
<<==Average Number of Speeches Collected per Day==
SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
Deletions:
<<SELECT AVG (a.count) A, AVG (b.count) B, AVG (c.count) C
