Re: Aggregate functions on groups

On Sat, 31 Aug 2019, Morris de Oryx wrote:

> Your tributaries and fish master tables make sense. If I read your code
> right, you're grouping by too many columns. I flattened the data into a
> survey table for this simple example:

Morris,

I'm still learning about postgres groups. My approach is to group on the
column of interest, then add more when psql tells me to do so.

> select tributary,
> common_name,
> scientific_name,
> sum(count_value) as fish_seen,
> count(count_value) as observations_made
>
> from survey
>
> group by 1,2,3 -- The GROUP BY clause can use positions on the select
> list, if you feel like typing less.

I will look more at this approach; at first glance it appears to address one
query but not all those needed.

> But this is not why I'm answering. I'm responding as I wanted to make sure
> that you're aware of the pg-similarity extension:
> https://salsa.debian.org/postgresql/pg-similarity

Thanks for the URL. I'll definintely read about similarity.

Regards,

Rich