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

CREATE OR REPLACE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS

SELECT DISTINCT p.project_name, pm."SiteCode"

AND po."NoInd">1

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

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

CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS

SELECT DISTINCT p.project_name, pm."SiteCode"

AND cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

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

CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS

SELECT DISTINCT p.project_name, pm."SiteCode"

AND intercept_cm IS NOT NULL

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS

SELECT p.project_name, "SiteCode" AS plotcode,

SUM("NoInd") AS individuals

GROUP BY p.project_name, "SiteCode"

ORDER BY p.project_name, "SiteCode"

LIMIT 10

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon

) AS a

GROUP BY project_name, plotcode

ORDER BY project_name, plotcode

IF(infra_ep_1 IS NULL,IFNULL(auth,''),

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

;

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

SUM("NoInd") AS individuals

GROUP BY p.project_name, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

AVG(cover_percent) AS mean_cover

AND cover_percent IS NOT NULL

GROUP BY p.project_name, "SiteCode", taxon

ORDER BY p.project_name, "SiteCode", taxon

TRIM(CONCAT_WS(' ',IFNULL(infra_rank_1,''),IFNULL(infra_ep_1,''),IFNULL(infra_auth_1,'')))

))) AS taxon,

intercept_cm

AND intercept_cm IS NOT NULL

ORDER BY p.project_name, "SiteCode", taxon

LIMIT 10

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

CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS

SELECT project_name, pm."SiteCode" AS plotcode, COUNT(DISTINCT "Line") as subplots

GROUP BY project_name, pm."SiteCode"

ORDER BY project_name, pm."SiteCode"

LIMIT 10

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

CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS

SELECT DISTINCT project_name, pm."SiteCode" AS plotcode, "Line" as subplot

AND pm."SiteCode" IS NOT NULL

ORDER BY project_name, pm."SiteCode", subplot

LIMIT 10