Revision 7297
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.sql | ||
|---|---|---|
| 3753 | 3753 |
-- |
| 3754 | 3754 |
|
| 3755 | 3755 |
CREATE VIEW provider_count_view AS |
| 3756 |
SELECT s.level, s.provider, s.count, COALESCE(source.sourcetype, 'herbarium'::sourcetype) AS sourcetype, COALESCE(source.observationtype, 'specimen'::observationtype) AS observationtype FROM ((SELECT 'top-level'::text AS level, analytical_stem.datasource AS provider, count(*) AS count FROM analytical_stem GROUP BY analytical_stem.datasource UNION ALL SELECT 'data owner'::text AS level, sourcename.name AS provider, count(*) AS count FROM ((sourcename JOIN sourcelist USING (sourcelist_id)) JOIN specimenreplicate ON ((specimenreplicate.institution_id = sourcelist.sourcelist_id))) GROUP BY sourcename.name HAVING (count(*) > 0)) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider;
|
|
| 3756 |
SELECT s.level, s.provider, s.count, source.sourcetype, source.observationtype FROM ((SELECT 'top-level'::text AS level, analytical_stem.datasource AS provider, count(*) AS count FROM analytical_stem GROUP BY analytical_stem.datasource UNION ALL SELECT 'data owner'::text AS level, sourcename.name AS provider, count(*) AS count FROM ((sourcename JOIN sourcelist USING (sourcelist_id)) JOIN specimenreplicate ON ((specimenreplicate.institution_id = sourcelist.sourcelist_id))) GROUP BY sourcename.name HAVING (count(*) > 0)) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider;
|
|
| 3757 | 3757 |
|
| 3758 | 3758 |
|
| 3759 | 3759 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: provider_count_view: Don't set default values for sourcetype/observationtype, because the appropriate values are now set for all top-level inputs and these defaults are not applicable for data owners not in geoscrub.herbaria