This is a quink demo of how to use the CSV data published by the Central Office of Information on Central Government Websites 2009-2010 as a database by re-publishing it via a Google Speadsheet and then interrogating via the Google visualization API.
If you are prompted to sign in, wait a few seconds and then try again.
The following are the column headings from the spreadsheet...
|select input||where input||Comments|
|B,C,I||Display columns B, C and I|
|B,C,I||I=23083||Display columns B, C and I for MPs claiming exactly 23083 in column 83|
|count(I)||I=23083||Count how many people claimed exactly 23083 in column I|
|B,C,I||I!=23083 order by I||display the people who did not claim exactly 23083 in column I and display them in increasing order of column I values|
|B,C,I||I!=23083 order by I desc||display the people who did not claim exactly 23083 in column I and display them in decreasing order of column I values|
|B,C,D,E||(C contains 'Joan' or C matches 'John')||Select by name (case sensitive); 'matches' must match exactly, 'contains' is a free text search|
|*||F<100000||full details of everyone who claimed less than 100000 in column F|
|sum(I)||Total claimed within column I|
|count(I)||number of rows where there's a value in column I|
|count(I)||where I>=0||make sure we only count 'valid' rows.|
|sum(I)/count(I)||Calculate the average amount claimed in column I|
|D,sum(I)||I>=0 group by D||Find out how much has been claimed by each party named in column D|
|D,sum(I)/count(I)||I>=0 group by D||for the total claimed by each party (column D), how much on average does each member of that party claim|