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:

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.


tributary                              common_name     scientific_name
   fish_seen      observations_made
Anderson Creek trib to Nehalem River   Black crappie   Pomoxis
nigromaculatus         3                      2
Anderson Creek trib to Nehalem River   Brook trout     Salvelinus
fontinalis          3                      2
Anderson Creek trib to Nehalem River   Bluegill        Lepomis macrochirus
           3                      2
Anderson Creek trib to Nehalem River   Brown bullhead  Ameiurus nebulosus
          3                      2

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

This tool implements a *lot* of similarity measures for fuzzy cmparisons.
Some are sting-oriented algorithms (Jaro-Winkler, Soundex, Levenshtein,
etc.), and others derive from and/or apply to field population comparisons,
like the Jaccard and Dice Coefficients. There's a lot of great stuff in the
package.

On Sat, Aug 31, 2019 at 3:14 AM Rich Shepard <rshep...@appl-ecosys.com>
wrote:

> Tables hold data on fish counts by stream name, species, and (unreported)
> collection dates. I'm trying to write a query that returns the total number
> of each species in each stream.
>
> The latest attempt is (lines wrapped by alpine; submitted as one line):
>
> \copy (select f.stream_tribs, f.count_value, sum(f.count_value),
> i.common_name, i.sci_name  from fish_counts as f, itis as i where
> f.stream_tribs like '%Nehalem River%' group by f.stream_tribs,
> i.common_name, i.sci_name, f.count_value  order by f.stream_tribs,
> i.common_name, i.sci_name, f.count_value) to
> '/home/rshepard/projects/oregon/mohler_sand/data/fish/fishes.dat';
>
> The returned set starts this way:
>
> Anderson Creek trib to Nehalem River    0       0       Black crappie
>  Pomoxis nigromaculatus
> Anderson Creek trib to Nehalem River    3       3       Black crappie
>  Pomoxis nigromaculatus
> Anderson Creek trib to Nehalem River    0       0       Bluegill
> Lepomis macrochirus
> Anderson Creek trib to Nehalem River    3       3       Bluegill
> Lepomis macrochirus
> Anderson Creek trib to Nehalem River    0       0       Brook trout
>  Salvelinus fontinalis
> Anderson Creek trib to Nehalem River    3       3       Brook trout
>  Salvelinus fontinalis
> Anderson Creek trib to Nehalem River    0       0       Brown bullhead
> Ameiurus nebulosus
> Anderson Creek trib to Nehalem River    3       3       Brown bullhead
> Ameiurus nebulosus
>
> What I want returned would look like this:
>
> Anderson Creek trib to Nehalem River  Black crappie  Pomoxis
> nigromaculatus 3
> Anderson Creek trib to Nehalem River  Bluegill       Lepomis macrochirus
>   3
> Anderson Creek trib to Nehalem River  Brook trout    Salvelinus
> fontinalis  3
> Anderson Creek trib to Nehalem River  Brown bullhead Ameiurus nebulosus
>  3
>
> I've read the manual yet must have not seen the section explaining how to
> apply aggregate functions to groups.
>
> Thanks in advance,
>
> Rich
>
>
>

Reply via email to