From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew D <andrew.do1613@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres Bug - Aggregate with order by
In-reply-to: 
 <CAD9rp8B=YkedfmZhn5aRjcmm75s15y78g7jk45FShPvMPbFb3A@mail.gmail.com>
References: 
 <CAD9rp8B=YkedfmZhn5aRjcmm75s15y78g7jk45FShPvMPbFb3A@mail.gmail.com>
Comments: In-reply-to Andrew D <andrew.do1613@gmail.com>
	message dated "Thu, 31 Mar 2022 13:04:03 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <731794.1648758842.1@sss.pgh.pa.us>
Date: Thu, 31 Mar 2022 16:34:02 -0400
Message-ID: <731795.1648758842@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/731795.1648758842%40sss.pgh.pa.us>
Precedence: bulk

Andrew D <andrew.do1613@gmail.com> writes:
> I believe that I've found a bug within postgres where an aggregate function
> is not accepted when an order by clause is specified, here is a short
> example:

> postgres=# select COUNT(i) from test_table order by i;
> ERROR:  column "test_table.i" must appear in the GROUP BY clause or be used
> in an aggregate function
> LINE 1: select COUNT(i) from test_table order by i;
>                                                  ^

This looks perfectly normal to me.  The ORDER BY clause is
supposed to be performed after the aggregation step.  But
there's no meaningful value of i to associate with the
aggregated rows (well, row, in this case).

It's not very clear what you were hoping to accomplish, but
if you intended to sort before the aggregation, the correct
way is to write

select COUNT(i order by i) from test_table;

(Ordering the input to COUNT() is pretty pointless, of course,
but there are other aggregates for which it's useful to do this.)

			regards, tom lane