Project

General

Profile

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
;