count distinct and group by

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz <mabewlun@gmail.com> wrote:

Hi,
I'm not sure why there is a reason for such behaviour.

For this table:

create table bg(id serial primary key, t text);

This works:

select count(id) from bg;

This works:

select count(distinct id) from bg;

And this doesn't:

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;

There is no "id" column in the returned dataset to order by. You are just
returning one value, how would it be ordered? (and that row has a column
named "count" - but you can alias it to SELECT count(distinct id) AS id
FROM bg ORDER BY id - it just makes no sense to order a single row..

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 7 May 2015 at 11:23, Szymon Guz <mabewlun@gmail.com> 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

And this doesn't:

select count(distinct id) from bg order by id;
ERROR: column "bg.id <http://bg.id&gt;&quot; 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;

Your result set will contain one row with the count of distinct ids.
You can't really order 1 row.

The error message occurs because your result set has one unnamed column:
count(distinct id). You could write the query like:

select count(distinct id) as cnt from bg order by cnt;

That would be correct SQL, because the column "cnt" now does exist.

Kind regards,
Andomar

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 7 May 2015 at 12:39, Magnus Hagander <magnus@hagander.net> wrote:

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz <mabewlun@gmail.com> wrote:

Hi,
I'm not sure why there is a reason for such behaviour.

For this table:

create table bg(id serial primary key, t text);

This works:

select count(id) from bg;

This works:

select count(distinct id) from bg;

And this doesn't:

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;

There is no "id" column in the returned dataset to order by. You are just
returning one value, how would it be ordered? (and that row has a column
named "count" - but you can alias it to SELECT count(distinct id) AS id
FROM bg ORDER BY id - it just makes no sense to order a single row..

Oh, right. Thanks. I haven't noticed that there is no id column in the
dataset.

thanks,
Szymon

Geoff Winkless schrieb am 07.05.2015 um 12:39:

in Postgres (unlike MySQL) you can't order a list of values by a column you haven't selected.​

Of course you can, just not when you are aggregating.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 7 May 2015 at 11:54, Thomas Kellerer <spam_eater@gmx.net> wrote:

Geoff Winkless schrieb am 07.05.2015 um 12:39:

in Postgres (unlike MySQL) you can't order a list of values by a column

you haven't selected.​

Of course you can, just not when you are aggregating.

​Doh! I missed out that key clause :)

Thanks for correcting me.

Geoff​