1 |
1243
|
aaronmk
|
SELECT '# specimen replicates' AS ___;
|
2 |
|
|
SELECT count(*) AS count
|
3 |
|
|
FROM specimenreplicate
|
4 |
1249
|
aaronmk
|
WHERE datasource_id =
|
5 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
6 |
1243
|
aaronmk
|
;
|
7 |
|
|
|
8 |
|
|
SELECT '# families' AS ___;
|
9 |
1593
|
aaronmk
|
SELECT count(DISTINCT plantname.plantname_id) AS count
|
10 |
1243
|
aaronmk
|
FROM specimenreplicate
|
11 |
|
|
JOIN taxondetermination USING (taxonoccurrence_id)
|
12 |
|
|
JOIN plantconcept USING (plantconcept_id)
|
13 |
1593
|
aaronmk
|
JOIN plantname_ancestor USING (plantname_id)
|
14 |
1246
|
aaronmk
|
JOIN plantname
|
15 |
1593
|
aaronmk
|
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
16 |
|
|
AND plantname.rank = 'family'
|
17 |
1249
|
aaronmk
|
WHERE datasource_id =
|
18 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
19 |
|
|
AND taxondetermination.isoriginal
|
20 |
1243
|
aaronmk
|
;
|
21 |
1246
|
aaronmk
|
|
22 |
|
|
SELECT '# genera' AS ___;
|
23 |
1593
|
aaronmk
|
SELECT count(DISTINCT plantname.plantname_id) AS count
|
24 |
1246
|
aaronmk
|
FROM specimenreplicate
|
25 |
|
|
JOIN taxondetermination USING (taxonoccurrence_id)
|
26 |
|
|
JOIN plantconcept USING (plantconcept_id)
|
27 |
1593
|
aaronmk
|
JOIN plantname_ancestor USING (plantname_id)
|
28 |
1246
|
aaronmk
|
JOIN plantname
|
29 |
1593
|
aaronmk
|
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
30 |
|
|
AND plantname.rank = 'genus'
|
31 |
1249
|
aaronmk
|
WHERE datasource_id =
|
32 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
33 |
|
|
AND taxondetermination.isoriginal
|
34 |
1246
|
aaronmk
|
;
|
35 |
|
|
|
36 |
|
|
SELECT '# species' AS ___;
|
37 |
1596
|
aaronmk
|
SELECT count(DISTINCT plantname.plantname_id) AS count
|
38 |
1246
|
aaronmk
|
FROM specimenreplicate
|
39 |
1593
|
aaronmk
|
JOIN taxondetermination USING (taxonoccurrence_id)
|
40 |
|
|
JOIN plantconcept USING (plantconcept_id)
|
41 |
1596
|
aaronmk
|
JOIN plantname_ancestor USING (plantname_id)
|
42 |
|
|
JOIN plantname
|
43 |
|
|
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
44 |
|
|
AND plantname.rank = 'species'
|
45 |
1249
|
aaronmk
|
WHERE datasource_id =
|
46 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
47 |
1596
|
aaronmk
|
AND taxondetermination.isoriginal
|
48 |
1246
|
aaronmk
|
;
|
49 |
1254
|
aaronmk
|
|
50 |
|
|
SELECT '# binomials' AS ___;
|
51 |
1593
|
aaronmk
|
SELECT count(DISTINCT plantname.plantname_id) AS count
|
52 |
1254
|
aaronmk
|
FROM specimenreplicate
|
53 |
|
|
JOIN taxondetermination USING (taxonoccurrence_id)
|
54 |
|
|
JOIN plantconcept USING (plantconcept_id)
|
55 |
1593
|
aaronmk
|
JOIN plantname_ancestor USING (plantname_id)
|
56 |
1254
|
aaronmk
|
JOIN plantname
|
57 |
1593
|
aaronmk
|
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
58 |
|
|
AND plantname.rank = 'binomial'
|
59 |
1254
|
aaronmk
|
WHERE datasource_id =
|
60 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
61 |
|
|
AND taxondetermination.isoriginal
|
62 |
|
|
;
|
63 |
1297
|
aaronmk
|
|
64 |
|
|
SELECT 'collection codes' AS ___;
|
65 |
|
|
SELECT DISTINCT collectioncode_dwc
|
66 |
|
|
FROM specimenreplicate
|
67 |
|
|
WHERE datasource_id =
|
68 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
69 |
|
|
;
|
70 |
|
|
|
71 |
|
|
SELECT '# catalog numbers' AS ___;
|
72 |
|
|
SELECT count(DISTINCT catalognumber_dwc) AS count
|
73 |
|
|
FROM specimenreplicate
|
74 |
|
|
WHERE datasource_id =
|
75 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
76 |
|
|
;
|
77 |
1298
|
aaronmk
|
|
78 |
1300
|
aaronmk
|
SELECT 'duplicate catalog numbers' AS ___;
|
79 |
|
|
SELECT catalognumber_dwc, count(*) AS count
|
80 |
|
|
FROM specimenreplicate
|
81 |
|
|
WHERE datasource_id =
|
82 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
83 |
|
|
GROUP BY catalognumber_dwc
|
84 |
|
|
HAVING count(*) > 1
|
85 |
1302
|
aaronmk
|
ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */
|
86 |
1300
|
aaronmk
|
;
|
87 |
|
|
|
88 |
1298
|
aaronmk
|
SELECT '# counties' AS ___;
|
89 |
1593
|
aaronmk
|
SELECT count(DISTINCT namedplace.namedplace_id) AS count
|
90 |
1298
|
aaronmk
|
FROM specimenreplicate
|
91 |
|
|
JOIN taxonoccurrence USING (taxonoccurrence_id)
|
92 |
|
|
JOIN locationevent USING (locationevent_id)
|
93 |
1325
|
aaronmk
|
JOIN locationdetermination USING (location_id)
|
94 |
1593
|
aaronmk
|
JOIN namedplace_ancestor USING (namedplace_id)
|
95 |
|
|
JOIN namedplace
|
96 |
|
|
ON namedplace.namedplace_id = namedplace_ancestor.ancestor_id
|
97 |
|
|
AND namedplace.rank = 'county'
|
98 |
1298
|
aaronmk
|
WHERE specimenreplicate.datasource_id =
|
99 |
|
|
(SELECT party_id FROM party WHERE organizationname = :datasource)
|
100 |
|
|
;
|