Using SQL

What this site runs on

The site is built with Datasette, software designed by Stanford University Knight fellow Simon Willison. It sits on top of SQLite, a database that uses structured query language — SQL — to ask questions of the data.

Filtering with the dropdowns

Open any table and you get three dropdowns. They are the fastest way in, and you do not need to write any SQL to use them.

1. Pick a column, such as city.
2. Pick an operator. = matches exactly and != returns everything that does not. Contains, starts with and ends with are the most useful for names. Use the greater-than and less-than operators on contribution amounts.
3. Type a search term. This field is case sensitive: denver returns nothing, while Denver returns results.

You can filter on several columns at once — for example a committee name plus amounts over 10,000.

Writing your own queries

This one pulls contributors from the state House contribution table, sums each donor’s giving and sorts from largest to smallest. Keep in mind that names are not standardized, so one donor may appear under several spellings.

select contributor, sum(contribution_amount) as total
from house_cont
group by contributor
order by total desc

To get totals by district:

select District, candidate_name, Party,
       sum(contribution_amount) as total
from house_cont
group by District, candidate_name, Party
order by total desc

Super PACs need one extra step. The table holds both 527 committees and independent expenditure committees, and a 527 often passes money to an IEC of the same name — so group by committee_type to keep the two apart:

select committee, committee_type, sum(contribution_amount) as total
from super_pacs_cont
group by committee, committee_type
order by total desc

Downloading results

Any set of results can be downloaded as CSV, best for a spreadsheet, or as JSON. If you are saving a CSV, use the advanced export option and click “download file.”

Learning more

A Gentle Introduction to SQL is a good tutorial if you want to go further.

Questions and suggestions

Email fish: fishnette (at) gmail (dot) com