-- -------------------------------------------------------------------------------

-- Quantitative validation queries against the trait table in db bien2 on nimoy

-- 

-- For all queries, omit records where taxon or traitName are null or blank

-- This filtering would have been performed prior to import

-- -------------------------------------------------------------------------------

USE bien2_staging;

-- ------------------

-- 1. Count records

-- ------------------

SELECT COUNT(*) AS totalrecords

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

;

-- ------------------

-- 2. Count trait names

-- ------------------

SELECT COUNT(DISTINCT TraitName) AS traits

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

;

-- ------------------

-- 3. List trait names

-- ------------------

SELECT DISTINCT TraitName AS trait

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

ORDER BY trait

;

-- ------------------

-- 4. Count records per trait

-- ------------------

SELECT TraitName AS trait, COUNT(*) AS measurements

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

GROUP BY trait

ORDER BY trait

;

-- ------------------

-- 5. Count taxa

-- ------------------

SELECT COUNT(DISTINCT 

TRIM(CONCAT_WS(' ',IFNULL(Taxon,''),IFNULL(AuthorOfScientificName,'')))

) AS taxa

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

;

-- ------------------

-- 6. List distinct taxa

-- ------------------

SELECT DISTINCT 

TRIM(CONCAT_WS(' ',IFNULL(Taxon,''),IFNULL(AuthorOfScientificName,'')))

AS taxonwithauthor

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

ORDER BY taxonwithauthor

;

-- ------------------

-- 7. Trait, value and units 

-- ------------------

SELECT TraitName AS trait, TraitValue AS value, Unit AS units

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

ORDER BY trait, `value`, units

;

-- ------------------

-- 8. Taxon, trait and value for first 5000 records 

-- ------------------

SELECT 

TRIM(CONCAT_WS(' ',IFNULL(Taxon,''),IFNULL(AuthorOfScientificName,'')))

AS taxonwithauthor,

TraitName AS trait, TraitValue AS `value`

FROM TraitObservation

WHERE Taxon IS NOT NULL AND Taxon<>'' AND TraitName IS NOT NULL AND TraitName<>''

ORDER BY taxonwithauthor, trait, `value`

;