From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Aggregate functions on groups |
Date: | 2019-08-30 17:14:31 |
Message-ID: | alpine.LNX.2.20.1908301003500.22705@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2019-08-30 17:41:26 | Re: Aggregate functions on groups [RESOLVED] |
Previous Message | Rich Shepard | 2019-08-30 17:02:30 | Re: Escape parentheses in aggregate function |