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'
;