###############################################
-- Brad Boyle / SALVIAS plots validations
###############################################

-- extract of boyle-salvias plots for validation
-- these queries pull the original data from SALVIAS plots
-- project_id=2 is "SALVIAS:Boyle Transects"

USE salvias_plots;

-- Count of species per plot from three plots
SELECT PlotCode, COUNT(DISTINCT Family, Genus, Species) as totSpecies
FROM PlotMetadata p JOIN PlotObservations o
ON p.PlotID=o.PlotID
WHERE project_id=2
GROUP BY PlotCode
ORDER BY PlotCode;

-- List of species per plot, three plots
SELECT DISTINCT PlotCode, CONCAT(IF(Genus IS NULL OR Genus='',IFNULL(Family,''),''),' ',IFNULL(Genus,''),' ', IFNULL(Species,'')) AS morphospecies
FROM PlotObservations
WHERE PlotCode IN ('m2250-1','c2250-1','e2250-1')
ORDER BY PlotCode, morphospecies;

-- Count of individuals per species, same plots
SELECT 
PlotCode, 
CONCAT(IF(Genus IS NULL OR Genus='',IFNULL(Family,''),''),' ',IFNULL(Genus,''),' ', IFNULL(Species,'')) AS morphospecies,
SUM(NoInd) AS individuals
FROM PlotObservations
WHERE PlotCode IN ('m2250-1','c2250-1','e2250-1')
GROUP BY PlotCode, morphospecies
ORDER BY  PlotCode, morphospecies;

-- Count of stems >=2.5 cm dbh per individual, same plots
SELECT 
PlotCode, 
Ind as individualID, 
CONCAT(IF(Genus IS NULL OR Genus='',IFNULL(Family,''),''),' ',IFNULL(Genus,''),' ', IFNULL(Species,'')) AS morphospecies,
COUNT(DISTINCT stem_id) AS stems
FROM PlotObservations o JOIN stems s
ON o.PlotObsID=s.PlotObsID
WHERE PlotCode IN ('m2250-1','c2250-1','e2250-1')
GROUP BY PlotCode, morphospecies, individualID
ORDER BY  PlotCode, morphospecies, individualID;

-- Count of individuals with >= one stem >= 2.5 cm dbh per plot
SELECT 
PlotCode, SUM(DISTINCT Ind) AS individuals
FROM PlotMetadata p JOIN PlotObservations o JOIN stems s
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
WHERE project_id=2
AND stem_dbh>=2.5
GROUP BY PlotCode
ORDER BY  PlotCode;

-- Count of stems >= 2.5 cm dbh per plot
SELECT 
PlotCode, COUNT(DISTINCT stem_id) AS stems
FROM PlotMetadata p JOIN PlotObservations o JOIN stems s
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
WHERE project_id=2
AND stem_dbh>=2.5
GROUP BY PlotCode
ORDER BY  PlotCode;

-- Count of stems >=10 cm dbh per plot
SELECT 
PlotCode, COUNT(DISTINCT stem_id) AS stems
FROM PlotMetadata p JOIN PlotObservations o JOIN stems s
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
WHERE project_id=2
AND stem_dbh>=10
GROUP BY PlotCode
ORDER BY  PlotCode;