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 <[email protected]>
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
>
>
>
