[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Aggregate functions on groups [RESOLVED]
From:       Rich Shepard <rshepard () appl-ecosys ! com>
Date:       2019-08-30 17:41:26
Message-ID: alpine.LNX.2.20.1908301038010.22705 () salmo ! appl-ecosys ! com
[Download RAW message or body]

On Fri, 30 Aug 2019, John W Higgins wrote:

> You are grouping by count_value which means that you are asking the system
> to return a row for each different count_value.

John,

I didn't realize this.

> So if you remove the f.count_value from the select statement (not the
> sum(f.count_value)) - and you remove f.count_value from the group_by and
> order_by statements - you should get what you want

Aha. I thought I had to select f.count_value in order to obtain
sum(f.count_value); it's been a long time since I needed to do something
like this.

> Something like
>
> \copy (select f.stream_tribs, 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  order by f.stream_tribs,
> i.common_name, i.sci_name) to
> '/home/rshepard/projects/oregon/mohler_sand/data/fish/fishes.dat';

Thanks very much!

Rich


[prev in list] [next in list] [prev in thread] [next in thread]