On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard <rshep...@appl-ecosys.com> wrote:
> 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. > Put what you have in a subquery and group/aggregate again. select sample_count, count( site_nbr ) FROM ( select site_nbr, count(distinct sampdate) AS sample_count from wrb_hg_cong group by site_nbr order by site_nbr ) sub group by sample_count;
