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(*) AS count FROM plotMetadata;
19

    
20
SELECT '# location observations' AS ___;
21
SELECT count(*) AS count FROM plotMetadata;
22

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

    
33
SELECT '1st method narratives' AS ___;
34
SELECT DISTINCT
35
    CAST(projects.project_id AS char) AS project
36
    , PlotMethod AS method_narrative
37
FROM projects
38
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
39
ORDER BY project, method_narrative
40
LIMIT 10;
41

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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