Project

General

Profile

1 373 aaronmk
SELECT '# projects' AS ___;
2 371 aaronmk
SELECT count(*) AS count FROM projects;
3 368 aaronmk
4 374 aaronmk
SELECT 'projects' AS ___;
5
SELECT CAST(project_id AS char) AS project FROM projects ORDER BY project;
6 368 aaronmk
7 616 aaronmk
SELECT 'each project\'s # locations' AS ___;
8 377 aaronmk
SELECT
9
    CAST(projects.project_id AS char) AS project
10 617 aaronmk
    , count(PlotID) AS locations_count
11 371 aaronmk
FROM projects
12
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
13
GROUP BY project
14
ORDER BY project
15
;
16
17 616 aaronmk
SELECT '# locations' AS ___;
18 1159 aaronmk
SELECT count(DISTINCT LatDec, LongDec) AS count FROM plotMetadata
19 1157 aaronmk
;
20 376 aaronmk
21 1083 aaronmk
SELECT '# location events' AS ___;
22 374 aaronmk
SELECT count(*) AS count FROM plotMetadata;
23
24 377 aaronmk
SELECT '1st methods' AS ___;
25
SELECT DISTINCT
26
    CAST(projects.project_id AS char) AS project
27 1083 aaronmk
    , PlotMethod AS method
28 377 aaronmk
FROM projects
29
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
30
ORDER BY project, method
31 368 aaronmk
LIMIT 10;
32
33 1083 aaronmk
SELECT '1st observation measures' AS ___;
34 377 aaronmk
SELECT DISTINCT
35
    CAST(projects.project_id AS char) AS project
36 1083 aaronmk
    , lookup_MethodCode.Description AS observationmeasure
37 377 aaronmk
FROM projects
38
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
39 1083 aaronmk
JOIN lookup_MethodCode ON lookup_MethodCode.MethodCode = plotMetadata.MethodCode
40
ORDER BY project, observationmeasure
41 377 aaronmk
LIMIT 10;
42
43
/*
44 368 aaronmk
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
*/