Postgres Pro
Downloads
Home   >   mailing lists

Thread: count distinct and group by

count distinct and group by

From
Szymon Guz
Date:
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;

thanks,
Szymon

Re: count distinct and group by

From
Magnus Hagander
Date:
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/

Re: count distinct and group by

From
Geoff Winkless
Date:
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

Re: count distinct and group by

From
Andomar
Date:
> And this doesn't:
>
> select count(distinct id) from bg order by id;
> ERROR:  column "bg.id <http://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;
>

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

Re: count distinct and group by

From
Szymon Guz
Date:


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 

Re: count distinct and group by

From
Thomas Kellerer
Date:
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.

Re: count distinct and group by

From
Geoff Winkless
Date:
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​