Aggregate functions on groups

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

Responses

Browse pgsql-general by date

  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