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 party ON party_id = datasource_id AND organizationname = :datasource
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||','||species.plantname) AS count
FROM specimenreplicate
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN plantconcept USING (plantconcept_id)
JOIN plantname AS genus
    ON genus.plantname_id = plantconcept.plantname_id
    AND genus.rank = 'Genus'
JOIN plantname AS species
    ON species.plantname_id = plantconcept.plantname_id
    AND species.rank = 'Species'
WHERE datasource_id =
    (SELECT party_id FROM party WHERE organizationname = :datasource)
AND taxondetermination.isoriginal
;