SELECT '# specimen replicates' AS ___;
SELECT count(*) AS count
FROM specimenreplicate
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# families' AS ___;
SELECT count(DISTINCT family) AS count
FROM taxonlabel
JOIN taxonverbatim USING (taxonlabel_id)
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# genera' AS ___;
SELECT count(DISTINCT genus) AS count
FROM taxonlabel
JOIN taxonverbatim USING (taxonlabel_id)
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# species' AS ___;
SELECT count(DISTINCT specific_epithet) AS count
FROM taxonlabel
JOIN taxonverbatim USING (taxonlabel_id)
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# binomials' AS ___;
SELECT count(DISTINCT taxonverbatim_id) AS count
FROM taxonlabel
JOIN taxonverbatim USING (taxonlabel_id)
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT 'collection codes' AS ___;
SELECT DISTINCT collectioncode_dwc
FROM specimenreplicate
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# catalog numbers' AS ___;
SELECT count(DISTINCT catalognumber_dwc) AS count
FROM specimenreplicate
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT 'duplicate catalog numbers' AS ___;
SELECT catalognumber_dwc, count(*) AS count
FROM specimenreplicate
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
GROUP BY catalognumber_dwc
HAVING count(*) > 1
ORDER BY catalognumber_dwc
;

SELECT '# counties' AS ___;
SELECT count(DISTINCT county) AS count
FROM place
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;