Project

General

Profile

1
SELECT '# specimen replicates' AS ___;
2
SELECT count(*) AS count FROM nybg_raw;
3

    
4
SELECT '# families' AS ___;
5
SELECT count(DISTINCT Family) AS count FROM nybg_raw;
6

    
7
SELECT '# genera' AS ___;
8
SELECT count(DISTINCT Genus) AS count FROM nybg_raw;
9

    
10
SELECT '# species' AS ___;
11
SELECT count(DISTINCT Genus, Species) AS count FROM nybg_raw;
12

    
13
/*
14
SELECT 
15
(
16
SELECT COUNT(DISTINCT Family) FROM nybg_raw WHERE Family IS NOT NULL
17
) AS families, 
18
(
19
SELECT COUNT(DISTINCT Genus) FROM nybg_raw WHERE Genus IS NOT NULL
20
) AS genera,
21
(
22
SELECT COUNT(DISTINCT Genus, Species) FROM nybg_raw 
23
WHERE Genus IS NOT NULL AND Species IS NOT NULL
24
) AS species;
25

    
26
SELECT DISTINCT CONCAT_WS(' ', Genus, Species) AS Species
27
FROM nybg_raw 
28
WHERE Genus IS NOT NULL AND Species IS NOT NULL;
29

    
30
SELECT DISTINCT Genus AS Genera
31
FROM nybg_raw 
32
WHERE Genus IS NOT NULL;
33

    
34
SELECT DISTINCT Family as Families
35
FROM nybg_raw 
36
WHERE Family IS NOT NULL;
37

    
38
SELECT ScientificName, Genus, Species, Subspecies, ScientificNameAuthor
39
FROM nybg_raw
40
LIMIT 25;
41

    
42
SELECT ScientificName, Genus, Species, Subspecies, ScientificNameAuthor
43
FROM nybg_raw
44
WHERE Subspecies IS NOT NULL
45
LIMIT 15;
46

    
47
SELECT InstitutionCode, COUNT(*) AS records
48
FROM nybg_raw
49
GROUP BY InstitutionCode;
50

    
51
SELECT DISTINCT CollectionCode 
52
FROM nybg_raw;
53

    
54
SELECT DISTINCT Country, StateProvince, County 
55
FROM nybg_raw
56
LIMIT 25;
57

    
58
SELECT COUNT(*) as totalRecords, COUNT(DISTINCT CatalogNumber) as uniqueCatalogNumbers
59
FROM nybg_raw;
60

    
61
SELECT CatalogNumber, COUNT(*) AS records
62
FROM nybg_raw
63
GROUP BY CatalogNumber
64
HAVING records>1;
65

    
66
SELECT b.CatalogNumber, DateLastModified, ScientificName, Collector, FieldNumber, 
67
CollectedDate
68
FROM nybg_raw AS a JOIN (
69
SELECT CatalogNumber, COUNT(*) AS records
70
FROM nybg_raw
71
WHERE CatalogNumber IS NOT NULL
72
GROUP BY CatalogNumber
73
HAVING records>1
74
) AS b
75
ON a.CatalogNumber=b.CatalogNumber
76
ORDER BY b.CatalogNumber ASC, DateLastModified DESC;
77

    
78
SELECT DateLastModified, ScientificName, Collector, FieldNumber, 
79
CollectedDate
80
FROM nybg_raw 
81
WHERE CatalogNumber IS NULL
82
ORDER BY CatalogNumber ASC, DateLastModified DESC
83
LIMIT 12;
84

    
85
SELECT 
86
(
87
SELECT COUNT(*)
88
FROM nybg_raw
89
WHERE Latitude IS NOT NULL
90
) AS allLats,
91
(
92
SELECT COUNT(*)
93
FROM nybg_raw
94
WHERE Latitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'
95
) AS decimalLats,
96
(
97
SELECT COUNT(*)
98
FROM nybg_raw
99
WHERE Longitude IS NOT NULL
100
) AS allLongs,
101
(
102
SELECT COUNT(*)
103
FROM nybg_raw
104
WHERE Longitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'
105
) AS decimalLongs;
106

    
107
SELECT Latitude, Longitude 
108
FROM nybg_raw
109
WHERE  Latitude IS NOT NULL AND  Longitude IS NOT NULL
110
LIMIT 10;
111

    
112
SELECT 
113
(
114
SELECT COUNT(*)
115
FROM nybg_raw
116
WHERE Latitude>90 OR Latitude<-90
117
) AS badLats,
118
(
119
SELECT COUNT(*)
120
FROM nybg_raw
121
WHERE Longitude>180 OR Longitude<-180
122
) AS badLongs;
123

    
124
SELECT DayCollected, MonthCollected, YearCollected 
125
FROM nybg_raw
126
WHERE DayCollected IS NOT NULL OR MonthCollected IS NOT NULL OR YearCollected IS NOT NULL
127
LIMIT 10;
128

    
129
SELECT
130
(
131
SELECT COUNT(*)
132
FROM nybg_raw
133
WHERE NOT(DayCollected REGEXP '^-?[0-9]+$')
134
OR NOT(MonthCollected REGEXP '^-?[0-9]+$')
135
OR NOT(YearCollected REGEXP '^-?[0-9]+$')
136
) AS badDateCollected,
137
(
138
SELECT COUNT(*)
139
FROM nybg_raw
140
WHERE NOT(DayIdentified REGEXP '^-?[0-9]+$')
141
OR NOT(MonthIdentified REGEXP '^-?[0-9]+$')
142
OR NOT(YearIdentified REGEXP '^-?[0-9]+$')
143
) AS badDateIdentified;
144

    
145
SELECT Locality
146
FROM nybg_raw
147
WHERE Locality IS NOT NULL
148
LIMIT 10;
149

    
150
SELECT PlantFungusDescription
151
FROM nybg_raw
152
WHERE PlantFungusDescription IS NOT NULL
153
LIMIT 10;
154

    
155
SELECT Habitat, Vegetation
156
FROM nybg_raw
157
WHERE Habitat IS NOT NULL OR Vegetation IS NOT NULL
158
LIMIT 10;
159
*/
(2-2/2)