Re: [GENERAL] count distinct and group by

On 7 May 2015 at 11:54, Thomas Kellerer 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 :)

Re: [GENERAL] count distinct and group by

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 you

Re: [GENERAL] count distinct and group by

On 7 May 2015 at 12:39, Magnus Hagander wrote: > On Thu, May 7, 2015 at 12:23 PM, Szymon Guz 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; >>

Re: [GENERAL] count distinct and group by

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; Your result set will contain one row with the count of distinct i

Re: [GENERAL] count distinct and group by

On 7 May 2015 at 11:23, Szymon Guz 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

Re: [GENERAL] count distinct and group by

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz 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 th

Re: [GENERAL] count distinct slow?

Roger Pack writes: > As a note, I ran into the following today (doing a select distinct is fast, > doing a count distinct is significantly slower?) The planner appears to prefer hash aggregation for the variants of your query wherein the DISTINCT becomes a separate plan step. This is evidently a

Re: [GENERAL] count (DISTINCT field) OVER ()

Tarlika Elisabeth Schmitz, 10.11.2011 11:24: SELECT id, name, delta, sum(case when rn = 1 then rn else null end) over() as distinct_id_count FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, row_number() OVER(partition by id) AS rn

Re: [GENERAL] count (DISTINCT field) OVER ()

On Thu, 10 Nov 2011 10:02:36 +0100 Thomas Kellerer wrote: >Tarlika Elisabeth Schmitz, 10.11.2011 00:52: >> I would like to implement the equivalent of "count (DISTINCT id) >> OVER ()": >> >>[...] >> >> produces result: >> id, name, delta, cnt >> 1787 Toomyvara 0.5 4 >> 1787 Toomevara

Re: [GENERAL] count (DISTINCT field) OVER ()

Tarlika Elisabeth Schmitz, 10.11.2011 00:52: I would like to implement the equivalent of "count (DISTINCT field) OVER ()": SELECT id, name, similarity(name, 'Tooneyvara') as delta, count (id) OVER() AS cnt FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.1 ORDER BY del

Re: [GENERAL] count (DISTINCT field) OVER ()

On Nov 9, 2011, at 18:52, Tarlika Elisabeth Schmitz wrote: > I would like to implement the equivalent of "count (DISTINCT field) OVER > ()": > > > SELECT > id, name, similarity(name, 'Tooneyvara') as delta, > count (id) OVER() AS cnt > FROM vtown > WHERE > similarity(name, 'Tooneyva

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo wrote: > On Fri, 26 Dec 2008 10:43:25 -0800 > David Fetter wrote: > > > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo > > wrote: > > > I noticed that starting from 8.2 the documentation at > > > http://www.postgresq

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 10:43:25 -0800 David Fetter wrote: > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo > wrote: > > I noticed that starting from 8.2 the documentation at > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > > say that multiple distinct expres

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

2008/12/26 Ivan Sergio Borgonovo : > On Fri, 26 Dec 2008 16:23:52 +0100 > "Pavel Stehule" wrote: > >> 2008/12/26 Ivan Sergio Borgonovo : >> > On Fri, 26 Dec 2008 15:46:48 +0100 >> > "Pavel Stehule" wrote: >> > >> >> count has only one argument, >> > >> > then what was changed between 8.1 and 8.2

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo wrote: > I noticed that starting from 8.2 the documentation at > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > say that multiple distinct expressions are supported > > aggregate_name (DISTINCT expression [, exp

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 16:23:52 +0100 "Pavel Stehule" wrote: > 2008/12/26 Ivan Sergio Borgonovo : > > On Fri, 26 Dec 2008 15:46:48 +0100 > > "Pavel Stehule" wrote: > > > >> count has only one argument, > > > > then what was changed between 8.1 and 8.2 to change the docs? > > None of the functions l

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

2008/12/26 Ivan Sergio Borgonovo : > On Fri, 26 Dec 2008 15:46:48 +0100 > "Pavel Stehule" wrote: > >> count has only one argument, > > then what was changed between 8.1 and 8.2 to change the docs? > None of the functions listed in: > http://www.postgresql.org/docs/8.2/static/functions-aggregate.ht

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 15:46:48 +0100 "Pavel Stehule" wrote: > count has only one argument, then what was changed between 8.1 and 8.2 to change the docs? None of the functions listed in: http://www.postgresql.org/docs/8.2/static/functions-aggregate.html seems to support aggregate(distinct exp [,exp

Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation

Hello count has only one argument, try: postgres=# select * from fooa; a | b + 10 | 20 (1 row) postgres=# select count(distinct a,b) from fooa; ERROR: function count(integer, integer) does not exist LINE 1: select count(distinct a,b) from fooa; ^ HINT: No function ma

Re: [GENERAL] Count & Distinct

Yup.. I (original poster) am using 6.5.3 and should/would/will head to 7.0.2 (already on another site in production). Just waiting for the time/need. Thanks to everyone for their help. J > From: "Ryan Williams" <[EMAIL PROTECTED]> > Date: Thu, 24 Aug 2000 12:08:46 -0700 > To: <[EMAIL PROTECTED]

Re: [GENERAL] COUNT DISTINCT

> Bruce Momjian wrote: > > Hey, we didn't design SQL, we just implement it. :-) Oh, that's quotable... ;) - Jeff -- [EMAIL PROTECTED] - http://linux.conf.au/ -- linux.conf.au - coming to Sydney in January 2001 Installing Linux Around A

Re: [GENERAL] count & distinct

Andrea Aime wrote: > > Hi people. I would like to make a query that > tells me how many distinct values there are > in a column. Standard count doesn't seems > to support a count distinct option. > select distint count(*) of course doens't > work (distinti clause is applied after the > result are

RE: [GENERAL] count & distinct

> Hi people. I would like to make a query that > tells me how many distinct values there are > in a column. Standard count doesn't seems > to support a count distinct option. > select distint count(*) of course doens't > work (distinti clause is applied after the > result are calculated). I've