Project

General

Profile

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

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

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

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

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

    
42
SELECT '# locations' AS ___;
43
SELECT count(DISTINCT location_id) AS count
44
FROM location
45
WHERE source_id =
46
    (SELECT source_id FROM source WHERE shortname = :datasource)
47
;
48

    
49
SELECT '# location events' AS ___;
50
SELECT count(*) AS count
51
FROM locationevent
52
WHERE source_id =
53
    (SELECT source_id FROM source WHERE shortname = :datasource)
54
;
55

    
56
SELECT '1st methods' AS ___;
57
SELECT DISTINCT
58
    projectname
59
    , method.name AS method
60
FROM project
61
JOIN locationevent USING (project_id)
62
JOIN method USING (method_id)
63
WHERE project.source_id =
64
    (SELECT source_id FROM source WHERE shortname = :datasource)
65
ORDER BY projectname, method
66
LIMIT 10;
67

    
68
SELECT '1st observation measures' AS ___;
69
SELECT DISTINCT
70
    projectname
71
    , method.observationmeasure AS observationmeasure
72
FROM project
73
JOIN locationevent USING (project_id)
74
JOIN method USING (method_id)
75
WHERE project.source_id =
76
    (SELECT source_id FROM source WHERE shortname = :datasource)
77
ORDER BY projectname, observationmeasure
78
LIMIT 10;
(14-14/15)