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

On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard <[email protected]>
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;