mailto:bboyle@email.arizona.edu?Brad_Boyle.2014-1-21-9:28.Qualitative+validations+for+traits,+plus+revised+task+list.(bien3_validations_traits_bien3.sql)

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

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

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

USE bien2;

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

-- 1. Count records

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

SELECT COUNT(*) AS totalrecords

FROM TraitObservation;

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

-- 2. Count trait names

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

SELECT COUNT(DISTINCT TraitName) AS traits

FROM TraitObservation;

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

-- 3. List trait names

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

SELECT DISTINCT TraitName AS trait

FROM TraitObservation

ORDER BY TraitName;

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

-- 4. Count records per trait

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

SELECT TraitName AS trait, COUNT(*) AS measurements

FROM TraitObservation

GROUP BY TraitName

ORDER BY TraitName;

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

-- 5. Count taxa

-- 

-- Note: No morphospecies in trait table, therefore count

-- 		taxon + authority only

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

SELECT COUNT(DISTINCT TRIM(CONCAT(taxonCorrected,' ',IFNULL(authorityCorrected,'')))) AS taxa

FROM TraitObservation

WHERE taxonCorrected IS NOT NULL;

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

-- 6. List taxa

-- 

-- Note 1: No morphospecies in trait table, use taxon + authority 

-- Note 2: Note formation of taxonCorrected: includes family ONLY if

-- taxon is not determined at least to genus

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

SELECT DISTINCT TRIM(CONCAT(IFNULL(taxonCorrected,''),' ',IFNULL(authorityCorrected,''))) AS taxonwithauthor

FROM TraitObservation

ORDER BY taxonWithAuthor;

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

-- 7. Trait, value and units for first 5000 records 

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

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

FROM TraitObservation

ORDER BY TraitName, TraitValue, Unit

LIMIT 5000;

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

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

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

SELECT TRIM(CONCAT(IFNULL(taxonCorrected,''),' ',IFNULL(authorityCorrected,''))) AS taxonwithauthor,

TraitName AS trait, TraitValue AS value

FROM TraitObservation

ORDER BY taxonWithAuthor, TraitName, TraitValue

LIMIT 5000;