SELECT '# projects' AS ___; SELECT count(*) AS count FROM project WHERE datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) ; SELECT 'projects' AS ___; SELECT projectname AS project FROM project WHERE datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) ORDER BY project ; SELECT E'each project\'s # locations' AS ___; SELECT projectname AS project, count(*) AS locations_count FROM project JOIN locationevent USING (project_id) WHERE project.datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) GROUP BY project ORDER BY project ; SELECT '# locations' AS ___; SELECT count(DISTINCT location_id) AS count FROM locationevent WHERE datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) AND parent_id IS NULL -- no subplots ; SELECT '# location events' AS ___; SELECT count(*) AS count FROM locationevent WHERE datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) AND parent_id IS NULL -- no subplots ; SELECT '1st methods' AS ___; SELECT DISTINCT projectname AS project , method.name AS method FROM project JOIN locationevent USING (project_id) JOIN method USING (method_id) WHERE project.datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) ORDER BY project, method LIMIT 10; SELECT '1st observation measures' AS ___; SELECT DISTINCT projectname AS project , method.observationmeasure AS observationmeasure FROM project JOIN locationevent USING (project_id) JOIN method USING (method_id) WHERE project.datasource_id = (SELECT party_id FROM party WHERE organizationname = :datasource) ORDER BY project, observationmeasure LIMIT 10;