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

List:       pgsql-bugs
Subject:    Re: Postgres Bug - Aggregate with order by
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2022-03-31 20:34:02
Message-ID: 731795.1648758842 () sss ! pgh ! pa ! us
[Download RAW message or body]

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


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