Project

General

Profile

1
SELECT '# specimen replicates' AS ___;
2
SELECT count(*) AS count
3
FROM specimenreplicate
4
WHERE datasource_id =
5
    (SELECT party_id FROM party WHERE organizationname = :datasource)
6
;
7

    
8
SELECT '# families' AS ___;
9
SELECT count(DISTINCT plantname.plantname_id) AS count
10
FROM specimenreplicate
11
JOIN taxondetermination USING (taxonoccurrence_id)
12
JOIN plantconcept USING (plantconcept_id)
13
JOIN plantname_ancestor USING (plantname_id)
14
JOIN plantname
15
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
16
    AND plantname.rank = 'family'
17
WHERE datasource_id =
18
    (SELECT party_id FROM party WHERE organizationname = :datasource)
19
AND taxondetermination.isoriginal
20
;
21

    
22
SELECT '# genera' AS ___;
23
SELECT count(DISTINCT plantname.plantname_id) AS count
24
FROM specimenreplicate
25
JOIN taxondetermination USING (taxonoccurrence_id)
26
JOIN plantconcept USING (plantconcept_id)
27
JOIN plantname_ancestor USING (plantname_id)
28
JOIN plantname
29
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
30
    AND plantname.rank = 'genus'
31
WHERE datasource_id =
32
    (SELECT party_id FROM party WHERE organizationname = :datasource)
33
AND taxondetermination.isoriginal
34
;
35

    
36
SELECT '# species' AS ___;
37
SELECT count(DISTINCT plantname.plantname_id) AS count
38
FROM specimenreplicate
39
JOIN taxondetermination USING (taxonoccurrence_id)
40
JOIN plantconcept USING (plantconcept_id)
41
JOIN plantname_ancestor USING (plantname_id)
42
JOIN plantname
43
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
44
    AND plantname.rank = 'species'
45
WHERE datasource_id =
46
    (SELECT party_id FROM party WHERE organizationname = :datasource)
47
AND taxondetermination.isoriginal
48
;
49

    
50
SELECT '# binomials' AS ___;
51
SELECT count(DISTINCT plantname.plantname_id) AS count
52
FROM specimenreplicate
53
JOIN taxondetermination USING (taxonoccurrence_id)
54
JOIN plantconcept USING (plantconcept_id)
55
JOIN plantname_ancestor USING (plantname_id)
56
JOIN plantname
57
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
58
    AND plantname.rank = 'binomial'
59
WHERE datasource_id =
60
    (SELECT party_id FROM party WHERE organizationname = :datasource)
61
AND taxondetermination.isoriginal
62
;
63

    
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

    
78
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
ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */
86
;
87

    
88
SELECT '# counties' AS ___;
89
SELECT count(DISTINCT namedplace.namedplace_id) AS count
90
FROM specimenreplicate
91
JOIN taxonoccurrence USING (taxonoccurrence_id)
92
JOIN locationevent USING (locationevent_id)
93
JOIN locationdetermination USING (location_id)
94
JOIN namedplace_ancestor USING (namedplace_id)
95
JOIN namedplace
96
    ON namedplace.namedplace_id = namedplace_ancestor.ancestor_id
97
    AND namedplace.rank = 'county'
98
WHERE specimenreplicate.datasource_id =
99
    (SELECT party_id FROM party WHERE organizationname = :datasource)
100
;
(13-13/13)