/inputs/NYBG/verify/specimens.ref.sql - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 1297

specimens verification: Added collection codes and # catalog numbers tests

     SELECT '# binomials' AS ___;
     SELECT count(DISTINCT ScientificName) AS count FROM nybg_raw;
     /*
     SELECT DISTINCT CONCAT_WS(' ', Genus, Species) AS Species
     FROM nybg_raw
     WHERE Genus IS NOT NULL AND Species IS NOT NULL;
     SELECT 'collection codes' AS ___;
     SELECT DISTINCT CollectionCode AS collectioncode_dwc FROM nybg_raw;
     SELECT DISTINCT Genus AS Genera
     FROM nybg_raw
     WHERE Genus IS NOT NULL;
     SELECT '# catalog numbers' AS ___;
     SELECT count(DISTINCT CatalogNumber) AS count FROM nybg_raw;
     SELECT DISTINCT Family as Families
     FROM nybg_raw
     WHERE Family IS NOT NULL;
     SELECT ScientificName, Genus, Species, Subspecies, ScientificNameAuthor
     FROM nybg_raw
     LIMIT 25;
     SELECT ScientificName, Genus, Species, Subspecies, ScientificNameAuthor
     FROM nybg_raw
     WHERE Subspecies IS NOT NULL
     LIMIT 15;
     SELECT InstitutionCode, COUNT(*) AS records
     FROM nybg_raw
     GROUP BY InstitutionCode;
     SELECT DISTINCT CollectionCode
     FROM nybg_raw;
     /*
     SELECT DISTINCT Country, StateProvince, County
     FROM nybg_raw
     LIMIT 25;

Also available in: Unified diff