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