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) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname

    ON plantname.plantname_id = plantconcept.plantname_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) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname

    ON plantname.plantname_id = plantconcept.plantname_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||','||species_plantname.plantname)

        AS count

FROM specimenreplicate

JOIN taxondetermination AS genus_taxondet

    ON genus_taxondet.taxonoccurrence_id = specimenreplicate.taxonoccurrence_id

    AND genus_taxondet.isoriginal

JOIN plantconcept AS genus_plantconcept

    ON genus_plantconcept.plantconcept_id = genus_taxondet.plantconcept_id

JOIN plantname AS genus_plantname

    ON genus_plantname.plantname_id = genus_plantconcept.plantname_id

    AND genus_plantname.rank = 'Genus'

JOIN taxondetermination AS species_taxondet

    ON species_taxondet.taxonoccurrence_id =

        specimenreplicate.taxonoccurrence_id

    AND species_taxondet.isoriginal

JOIN plantconcept AS species_plantconcept

    ON species_plantconcept.plantconcept_id = species_taxondet.plantconcept_id

JOIN plantname AS species_plantname

    ON species_plantname.plantname_id = species_plantconcept.plantname_id

    AND species_plantname.rank = 'Species'

WHERE datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

;

SELECT '# binomials' AS ___;

SELECT count(DISTINCT plantname) AS count

FROM specimenreplicate

JOIN taxondetermination USING (taxonoccurrence_id)

JOIN plantconcept USING (plantconcept_id)

JOIN plantname

    ON plantname.plantname_id = plantconcept.plantname_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_id) AS count

FROM specimenreplicate

JOIN taxonoccurrence USING (taxonoccurrence_id)

JOIN locationevent USING (locationevent_id)

JOIN locationdetermination USING (location_id)

JOIN namedplace USING (namedplace_id)

WHERE specimenreplicate.datasource_id =

    (SELECT party_id FROM party WHERE organizationname = :datasource)

AND namedplace.rank = 'county'

;