Project

General

Profile

1
SELECT '# projects' AS ___;
2
SELECT count(*) AS count
3
FROM project
4
WHERE reference_id =
5
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
6
;
7

    
8
SELECT 'projects' AS ___;
9
SELECT projectname AS project
10
FROM project
11
WHERE reference_id =
12
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
13
ORDER BY project
14
;
15

    
16
SELECT E'each project\'s # locations' AS ___;
17
SELECT projectname AS project, count(*) AS locations_count
18
FROM project
19
JOIN locationevent USING (project_id)
20
WHERE project.reference_id =
21
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
22
GROUP BY project
23
ORDER BY project
24
;
25

    
26
SELECT '# locations' AS ___;
27
SELECT count(DISTINCT location_id) AS count
28
FROM locationevent
29
WHERE reference_id =
30
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
31
AND parent_id IS NULL -- no subplots
32
;
33

    
34
SELECT '# location events' AS ___;
35
SELECT count(*) AS count
36
FROM locationevent
37
WHERE reference_id =
38
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
39
AND parent_id IS NULL -- no subplots
40
;
41

    
42
SELECT '1st methods' AS ___;
43
SELECT DISTINCT
44
    projectname AS project
45
    , method.name AS method
46
FROM project
47
JOIN locationevent USING (project_id)
48
JOIN method USING (method_id)
49
WHERE project.reference_id =
50
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
51
ORDER BY project, method
52
LIMIT 10;
53

    
54
SELECT '1st observation measures' AS ___;
55
SELECT DISTINCT
56
    projectname AS project
57
    , method.observationmeasure AS observationmeasure
58
FROM project
59
JOIN locationevent USING (project_id)
60
JOIN method USING (method_id)
61
WHERE project.reference_id =
62
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
63
ORDER BY project, observationmeasure
64
LIMIT 10;
(12-12/13)