These SQL queries provide summaries of the information in the ODe/Hydroseek Metadata Catalog database.
Counts by Org
slow, not perfect
SELECT Sources.Organization,
count(DISTINCT SeriesCatalog.SiteID) as SiteCount,
count(DISTINCT VariableID) as VariableCount,
sum(cast(SeriesCatalog.Valuecount as bigint)) as ValueCount
FROM SeriesCatalog INNER JOIN Sources
ON SeriesCatalog.SourceID = Sources.SourceID
GROUP BY Sources.Organization order by valuecount desc
station counts by org
Station counts from HSODM database
SELECT OrganizationName, count(SiteCode) as siteCount
FROM Sites
group by OrganizationName
order by count(SiteCode) desc
Regions
SELECT OrganizationName,
count(DISTINCT SiteCode) as SiteCount, min(Latitude) as South, Max(latitude) as North,
min(longitude) as West, max(longitude) as East
FROM sites
GROUP BY OrganizationName order by SiteCount
values count by org
THIS IS AN ESTIMATED SUM
SELECT Sources.Organization, sum(SeriesCatalog.Valuecount) as ValueCount
FROM SeriesCatalog INNER JOIN
Sources ON SeriesCatalog.SourceID = Sources.SourceID
GROUP BY Sources.Organization
order by valuecount desc
variables and values count ¶
SELECT Sources.Organization,
count(DISTINCT SeriesCatalog.SiteID) as SiteCount,
count(DISTINCT VariableID) as VariableCount,
sum(SeriesCatalog.Valuecount) as ValueCount
FROM SeriesCatalog INNER JOIN Sources
ON SeriesCatalog.SourceID = Sources.SourceID
GROUP BY Sources.Organization order by valuecount desc
Site with variable count
SELECT Sources.Organization, SeriesCatalog.VariableName, COUNT(SeriesCatalog.SiteCode) AS SiteCount, sum(SeriesCatalog.ValueCount) As ValueCount
FROM SeriesCatalog INNER JOIN
Sources ON SeriesCatalog.SourceID = Sources.SourceID
GROUP BY Sources.Organization, SeriesCatalog.VariableName
ORDER BY Sources.Organization, SiteCount DESC
USGS DB queries
Use this one in sqlmanangement studio (variablename has commas.
SELECT VariableCode, usgs_station_type,
count(SiteID) as SeriesCount,
sum(Valuecount) as ValueCount
FROM odm_qw_SeriesCatalog
GROUP BY VariableCode, usgs_station_type order by valuecount desc
Use this one in excel
SELECT VariableCode, variablename, usgs_station_type,
count(SiteID) as SeriesCount,
sum(Valuecount) as ValueCount
FROM odm_qw_SeriesCatalog
GROUP BY VariableCode,variablename, usgs_station_type order by valuecount desc