Project

General

Profile

1 373 aaronmk
SELECT '# projects' AS ___;
2 1158 aaronmk
SELECT count(*) AS count
3
FROM project
4 5972 aaronmk
WHERE reference_id =
5
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
6 1158 aaronmk
;
7 368 aaronmk
8 374 aaronmk
SELECT 'projects' AS ___;
9 1158 aaronmk
SELECT projectname AS project
10
FROM project
11 5972 aaronmk
WHERE reference_id =
12
    (SELECT reference_id FROM reference WHERE shortname = :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 5972 aaronmk
WHERE project.reference_id =
21
    (SELECT reference_id FROM reference WHERE shortname = :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 5972 aaronmk
WHERE reference_id =
30
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
31 1249 aaronmk
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 5972 aaronmk
WHERE reference_id =
38
    (SELECT reference_id FROM reference WHERE shortname = :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 5972 aaronmk
WHERE project.reference_id =
50
    (SELECT reference_id FROM reference WHERE shortname = :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 5972 aaronmk
WHERE project.reference_id =
62
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
63 1083 aaronmk
ORDER BY project, observationmeasure
64 377 aaronmk
LIMIT 10;