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 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 = 'species'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
AND taxondetermination.isoriginal
;

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