Re: Counting number of sites with same number of sampling dates

On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard <rshepard(at)appl-ecosys(dot)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;