Re: count distinct and group by

Geoff Winkless <pgsqladmin@xxxxxxxx> · Thu, 7 May 2015 11:39:30 +0100

On 7 May 2015 at 11:23, Szymon Guz <mabewlun@xxxxxxxxx> wrote:
Hi,
I'm not sure why there is a reason for such behaviour.

select count(distinct id) from bg order by id;
ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(distinct id) from bg order by id;

Quite apart from the fact that you're trying to ORDER a recordset that contains a single row (why?), in Postgres (unlike MySQL) you can't order a list of values by a column you haven't selected.

Is this what you're trying to achieve:

SELECT COUNT(*), id FROM bg GROUP BY id ORDER BY id;

?

Geoff