Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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;

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux