Project

General

Profile

1
SELECT '# projects' AS ___;
2
SELECT count(*) AS count FROM projects;
3

    
4
SELECT 'projects' AS ___;
5
SELECT CAST(project_id AS char) AS project FROM projects ORDER BY project;
6

    
7
SELECT 'each project\'s # locations' AS ___;
8
SELECT
9
    CAST(projects.project_id AS char) AS project
10
    , count(PlotID) AS locations_count
11
FROM projects
12
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
13
GROUP BY project
14
ORDER BY project
15
;
16

    
17
SELECT '# locations' AS ___;
18
SELECT count(DISTINCT LatDec, LongDec) AS count FROM plotMetadata
19
;
20

    
21
SELECT '# location events' AS ___;
22
SELECT count(*) AS count FROM plotMetadata;
23

    
24
SELECT '1st methods' AS ___;
25
SELECT DISTINCT
26
    CAST(projects.project_id AS char) AS project
27
    , PlotMethod AS method
28
FROM projects
29
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
30
ORDER BY project, method
31
LIMIT 10;
32

    
33
SELECT '1st observation measures' AS ___;
34
SELECT DISTINCT
35
    CAST(projects.project_id AS char) AS project
36
    , lookup_MethodCode.Description AS observationmeasure
37
FROM projects
38
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
39
JOIN lookup_MethodCode ON lookup_MethodCode.MethodCode = plotMetadata.MethodCode
40
ORDER BY project, observationmeasure
41
LIMIT 10;
42

    
43
/*
44
SELECT p.PlotID, p.SiteCode, COUNT(*)
45
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
46
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
47
WHERE p.project_id=2
48
GROUP BY p.PlotID, p.SiteCode;
49

    
50
SELECT NoInd, COUNT(*) AS observations
51
FROM plotObservations o JOIN plotMetadata p 
52
ON o.PlotID=p.PlotID
53
WHERE project_id=2
54
GROUP BY NoInd;
55

    
56
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals
57
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
58
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
59
WHERE p.project_id=2
60
GROUP BY p.PlotID, p.SiteCode;
61

    
62
SELECT DISTINCT pr.project_id, project_name, PlotMethod, m.MethodCode, m.Description
63
FROM projects pr JOIN plotMetadata p JOIN lookup_MethodCode m
64
ON pr.project_id=p.project_id AND p.MethodCode=m.MethodCode
65
WHERE m.MethodCode=8;
66

    
67
SELECT NoInd, COUNT(*) AS observations
68
FROM plotObservations o JOIN plotMetadata p JOIN lookup_MethodCode m
69
ON o.PlotID=p.PlotID AND p.MethodCode=m.MethodCode
70
WHERE project_id=2
71
GROUP BY NoInd;
72

    
73
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals
74
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
75
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
76
WHERE p.project_id=1
77
GROUP BY p.PlotID, p.SiteCode
78
LIMIT 10;
79

    
80
SELECT PlotMethod, MethodCode, Description, COUNT(project_id) as projects
81
FROM (
82
SELECT DISTINCT p.PlotMethod, p.MethodCode, p.Description, p.project_id
83
FROM (
84
SELECT project_id, PlotID, PlotMethod, m.MethodCode, m.Description
85
FROM plotMetadata p JOIN lookup_MethodCode m
86
ON p.MethodCode=m.MethodCode
87
) p JOIN plotObservations o JOIN stems s
88
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
89
GROUP BY project_id
90
) AS a
91
GROUP BY PlotMethod, MethodCode, Description;
92

    
93
SELECT DISTINCT pr.project_id, project_name
94
FROM projects pr JOIN plotMetadata p JOIN plotObservations o JOIN stems s
95
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
96
WHERE p.MethodCode=3;
97

    
98
SELECT p.PlotID, SiteName as plot, COUNT(*) AS stems
99
FROM plotMetadata p JOIN plotObservations o JOIN stems s
100
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
101
WHERE p.project_id=2
102
GROUP BY p.PlotID, SiteName
103
LIMIT 10;
104

    
105
SELECT p.PlotID, SiteName AS plot, COUNT(DISTINCT Family, Genus, Species) AS SpeciesCount
106
FROM plotMetadata p JOIN plotObservations o
107
ON p.PlotID=o.PlotID
108
WHERE project_id=2
109
GROUP BY p.PlotID, SiteName;
110

    
111
SELECT DISTINCT p.PlotID, SiteName AS plot, Family, CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName
112
FROM plotMetadata p JOIN plotObservations o
113
ON p.PlotID=o.PlotID
114
WHERE p.PlotID=298;
115

    
116
SELECT name_status, count(*) FROM plotObservations GROUP BY name_status;
117

    
118
SELECT DISTINCT p.PlotID, SiteName AS plot, Family, 
119
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
120
FROM plotMetadata p JOIN plotObservations o
121
ON p.PlotID=o.PlotID
122
WHERE p.PlotID=298;
123

    
124
SELECT DISTINCT p.PlotID, SiteName AS plot, Family, 
125
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
126
FROM plotMetadata p JOIN plotObservations o
127
ON p.PlotID=o.PlotID
128
WHERE p.PlotID=298 AND name_status=1;
129

    
130
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, COUNT(*) AS individuals
131
FROM plotMetadata p JOIN plotObservations o
132
ON p.PlotID=o.PlotID
133
WHERE p.PlotID=298 
134
GROUP BY p.PlotID, plot, subplot;
135

    
136
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals
137
FROM plotMetadata p JOIN plotObservations o
138
ON p.PlotID=o.PlotID
139
WHERE p.PlotID=298
140
GROUP BY p.PlotID, plot, subplot;
141

    
142
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals
143
FROM plotMetadata p JOIN plotObservations o
144
ON p.PlotID=o.PlotID
145
WHERE p.PlotID=1
146
GROUP BY p.PlotID, plot, subplot;
147

    
148
SELECT COUNT(*) FROM plotMetadata WHERE MethodCode=1;
149

    
150
SELECT PlotID FROM plotMetadata WHERE MethodCode=1 LIMIT 1;
151

    
152
SELECT p.PlotID, SiteCode AS plot, Family, 
153
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, cover_percent
154
FROM plotMetadata p JOIN plotObservations o
155
ON p.PlotID=o.PlotID
156
WHERE p.PlotID=24589;
157

    
158
SELECT p.PlotID, p.SiteCode as plot, Country, PolDiv1 AS stateProvince, pol2 AS countyParish, 
159
LatDec as latitude, LongDec as longitude, Elev as elevation
160
FROM projects pr JOIN plotMetadata p
161
ON pr.project_id=p.project_id
162
WHERE pr.project_id=1
163
LIMIT 20;
164

    
165
SELECT AccessCode, COUNT(*)
166
FROM plotMetadata
167
GROUP BY AccessCode;
168

    
169
SELECT pr.project_id, project_name, project_pi, allow_download_all, PlotID, SiteCode, Country
170
FROM projects pr JOIN plotMetadata p
171
ON pr.project_id=p.project_id
172
WHERE p.AccessCode=1;
173
*/
(2-2/2)