Counting number of sites with same number of sampling dates
От | Rich Shepard |
---|---|
Тема | Counting number of sites with same number of sampling dates |
Дата | |
Msg-id | alpine.LNX.2.20.1912111226300.10822@salmo.appl-ecosys.com обсуждение исходный текст |
Ответы |
Re: Counting number of sites with same number of sampling dates
|
Список | pgsql-general |
A sampling location table has 28 distinct sites, each site being sampled from 1 to 67 times. I'm trying to obtain the number of sites having 1 sample, 2 samples, ... 67 samples and am not seeing the solution despite several alternative queries. The query, select site_nbr, count(distinct sampdate) from wrb_hg_cong group by site_nbr order by site_nbr; returns the number of times each site has been sampled, for example: site_nbr | count ----------+------- 10332 | 11 10335 | 1 10339 | 2 10340 | 1 10342 | 4 10344 | 18 10347 | 2 10348 | 2 10350 | 2 10351 | 1 10355 | 14 etc. I want the number of sites for each number of samples (e.g., how many sites with one sampdate, 2 sampdates, etc.). I cannot group by an aggregate such as count(distinct sampdate) yet I'm confident the syntax is simple and I'm not seeing how to get the number in each group. What is an appropriate select statement for this? TIA, Rich
В списке pgsql-general по дате отправления: