Aggregate functions on groups

Поиск
Список
Период
Сортировка
От Rich Shepard
Тема Aggregate functions on groups
Дата
Msg-id alpine.LNX.2.20.1908301003500.22705@salmo.appl-ecosys.com
обсуждение исходный текст
Ответы Re: Aggregate functions on groups  (Morris de Oryx <morrisdeoryx@gmail.com>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Rich Shepard
Дата:
Сообщение: Re: Escape parentheses in aggregate function
Следующее
От: Rich Shepard
Дата:
Сообщение: Re: Aggregate functions on groups [RESOLVED]