1 |
373
|
aaronmk
|
SELECT '# projects' AS ___;
|
2 |
1158
|
aaronmk
|
SELECT count(*) AS count
|
3 |
|
|
FROM project
|
4 |
1249
|
aaronmk
|
WHERE datasource_id =
|
5 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
6 |
1158
|
aaronmk
|
;
|
7 |
368
|
aaronmk
|
|
8 |
374
|
aaronmk
|
SELECT 'projects' AS ___;
|
9 |
1158
|
aaronmk
|
SELECT projectname AS project
|
10 |
|
|
FROM project
|
11 |
1249
|
aaronmk
|
WHERE datasource_id =
|
12 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
13 |
1158
|
aaronmk
|
ORDER BY project
|
14 |
|
|
;
|
15 |
371
|
aaronmk
|
|
16 |
616
|
aaronmk
|
SELECT E'each project\'s # locations' AS ___;
|
17 |
|
|
SELECT projectname AS project, count(*) AS locations_count
|
18 |
371
|
aaronmk
|
FROM project
|
19 |
616
|
aaronmk
|
JOIN locationevent USING (project_id)
|
20 |
1249
|
aaronmk
|
WHERE project.datasource_id =
|
21 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
22 |
371
|
aaronmk
|
GROUP BY project
|
23 |
|
|
ORDER BY project
|
24 |
|
|
;
|
25 |
374
|
aaronmk
|
|
26 |
616
|
aaronmk
|
SELECT '# locations' AS ___;
|
27 |
1158
|
aaronmk
|
SELECT count(DISTINCT location_id) AS count
|
28 |
|
|
FROM locationevent
|
29 |
1249
|
aaronmk
|
WHERE datasource_id =
|
30 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
31 |
|
|
AND parent_id IS NULL -- no subplots
|
32 |
1157
|
aaronmk
|
;
|
33 |
376
|
aaronmk
|
|
34 |
1083
|
aaronmk
|
SELECT '# location events' AS ___;
|
35 |
1158
|
aaronmk
|
SELECT count(*) AS count
|
36 |
|
|
FROM locationevent
|
37 |
1249
|
aaronmk
|
WHERE datasource_id =
|
38 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
39 |
1158
|
aaronmk
|
AND parent_id IS NULL -- no subplots
|
40 |
1157
|
aaronmk
|
;
|
41 |
377
|
aaronmk
|
|
42 |
|
|
SELECT '1st methods' AS ___;
|
43 |
|
|
SELECT DISTINCT
|
44 |
|
|
projectname AS project
|
45 |
1156
|
aaronmk
|
, method.name AS method
|
46 |
377
|
aaronmk
|
FROM project
|
47 |
616
|
aaronmk
|
JOIN locationevent USING (project_id)
|
48 |
1156
|
aaronmk
|
JOIN method USING (method_id)
|
49 |
1249
|
aaronmk
|
WHERE project.datasource_id =
|
50 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
51 |
377
|
aaronmk
|
ORDER BY project, method
|
52 |
|
|
LIMIT 10;
|
53 |
|
|
|
54 |
1083
|
aaronmk
|
SELECT '1st observation measures' AS ___;
|
55 |
377
|
aaronmk
|
SELECT DISTINCT
|
56 |
|
|
projectname AS project
|
57 |
1083
|
aaronmk
|
, method.observationmeasure AS observationmeasure
|
58 |
377
|
aaronmk
|
FROM project
|
59 |
616
|
aaronmk
|
JOIN locationevent USING (project_id)
|
60 |
1083
|
aaronmk
|
JOIN method USING (method_id)
|
61 |
1249
|
aaronmk
|
WHERE project.datasource_id =
|
62 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
63 |
1083
|
aaronmk
|
ORDER BY project, observationmeasure
|
64 |
377
|
aaronmk
|
LIMIT 10;
|