Página principal‎ > ‎Indicadores‎ > ‎

Help

Now it's time to write your own query in two parts - the columns you want to select, and the conditions you want each returned result to satisfy. So for example, the following table show possible queries over the Guardian Datastore MPs' Expenses spreadsheet (key:rvWgEEGK9xuUQBR1EFcxHWA)
select input where input Comments
*   Display everything
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
Estudiantes
B,D,E,H,K (G contains 'Fenomeno') Display Ph.D students of "Fenomenología" Group: Estudiantes, Fecha de Ingreso, Fecha de Salida, Asesor, Fecha de Sustentación
 G,B,D,E,H,J  order by G desc   Estudiantes y egresados  Maestría (Doctorado) en Física organizado por Grupo
 B,D,E,H,J  (G contains 'Fenomenolo' and (D contains '2007' or D contains '2008' or D contains '2009' or D contains '2010' or D contains '2011'))  Display MS.C students of "Fenomenología' Group with ' Fecha de Ingreso' between 2007 and 2011
 B,D,E,H,J  (R contains 'Sostenibilidad GFIF 2009')  Display MS.C Students associated to project "Sostebibilidad GFIF 2009"
B,D,E,H  (E matches '') order by D desc  Display active MS.C Students
 count(B)  (E matches '')  Number of active MS.C Students
 Visitantes
 B,C,D,E,F,H,I   (G contains 'GFIF' and E > date '2009-12-31' )  Display visitors of GFIF Group with Fecha Llegada  from 2010
 Publindex 2011
 B,C,D,E  (C contains 'EUROPEAN JOURNAL OF PHYSICS')  Display info of Journal in Publindex: "Listado de Revistas Homologadas" (very slow, be patient)
  B,C,D,E (lower(C) contains 'european journal of physics')Búsqueda independiente de mayúsculas (very slow, be patient) (ver: Official Google Help)
 B,C,D,E,F  (B matches '1434-6044')  Search Journal by ISSN
 Publicaciones
 count(C) (C > 2007 and R contains 'A1') Número de publicaciones desde el 2008 en revistas A1 [HTML]
 E,F,G,H,C,D  (M contains 'GFIF')  order by C desc Publications of "Grupo de Fenomenología de Interacciones Fundamentales" ordered by year (desc)
 cpunt(J) ((C contains '2007' or C contains '2008' or C contains '2009' or C contains '2010' or C contains '2011' or C contains '2012') and M contains 'GES:' and D contains 'Internacional')Number of international publications of 'Grupo de Estado Sólido' between 2007-2012

Try out some visualisation queries here...

Official Google Help

Based on code at http://ouseful.open.ac.uk/datastore/gspreadsheetdb2.php
Comments