Revision 7277
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 3749 | 3749 |
-- |
| 3750 | 3750 |
|
| 3751 | 3751 |
CREATE VIEW provider_count_view AS |
| 3752 |
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) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider; |
|
| 3752 |
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;
|
|
| 3753 | 3753 |
|
| 3754 | 3754 |
|
| 3755 | 3755 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: provider_count_view: Include only sources with at least one row. Currently (as of r7023), all entries in BIEN2's geoscrub.herbaria are also in VegBIEN, so the filter is not yet necessary, but switching to bien3_adb.ih could create source entries without data rows which should be excluded from the providers list.