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
