SELECT '# specimen replicates' AS ___;

SELECT count(*) AS count

FROM specimenreplicate

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;

SELECT '# families' AS ___;

SELECT count(DISTINCT plantname.plantname_id) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname_ancestor USING (plantname_id)

JOIN plantname

    ON plantname.plantname_id = plantname_ancestor.ancestor_id

    AND plantname.rank = 'family'

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

AND taxondetermination.isoriginal

;

SELECT '# genera' AS ___;

SELECT count(DISTINCT plantname.plantname_id) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname_ancestor USING (plantname_id)

JOIN plantname

    ON plantname.plantname_id = plantname_ancestor.ancestor_id

    AND plantname.rank = 'genus'

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

AND taxondetermination.isoriginal

;

SELECT '# species' AS ___;

SELECT

    count(DISTINCT genus_plantname.plantname_id

        ||','||species_plantname.plantname_id) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname_ancestor USING (plantname_id)

JOIN plantname AS genus_plantname

    ON genus_plantname.plantname_id = plantname_ancestor.ancestor_id

    AND genus_plantname.rank = 'genus'

JOIN plantname AS species_plantname

    ON species_plantname.plantname_id = plantname_ancestor.ancestor_id

    AND species_plantname.rank = 'species'

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;

SELECT '# binomials' AS ___;

SELECT count(DISTINCT plantname.plantname_id) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname_ancestor USING (plantname_id)

JOIN plantname

    ON plantname.plantname_id = plantname_ancestor.ancestor_id

    AND plantname.rank = 'binomial'

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

AND taxondetermination.isoriginal

;

SELECT 'collection codes' AS ___;

SELECT DISTINCT collectioncode_dwc

FROM specimenreplicate

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;

SELECT '# catalog numbers' AS ___;

SELECT count(DISTINCT catalognumber_dwc) AS count

FROM specimenreplicate

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;

SELECT 'duplicate catalog numbers' AS ___;

SELECT catalognumber_dwc, count(*) AS count

FROM specimenreplicate

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

GROUP BY catalognumber_dwc

HAVING count(*) > 1

ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */

;

SELECT '# counties' AS ___;

SELECT count(DISTINCT namedplace.namedplace_id) AS count

FROM specimenreplicate

JOIN taxonoccurrence USING (taxonoccurrence_id)

JOIN locationevent USING (locationevent_id)

JOIN locationdetermination USING (location_id)

JOIN namedplace_ancestor USING (namedplace_id)

JOIN namedplace

    ON namedplace.namedplace_id = namedplace_ancestor.ancestor_id

    AND namedplace.rank = 'county'

WHERE specimenreplicate.datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;