-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT ARRAY[po.census_date]) AS inventories

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   LEFT JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--