Re: [GENERAL] count distinct and group by

[Geoff Winkless]

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 :)

Thanks for correcting me.

Geoff​

Re: [GENERAL] count distinct and group by

[Thomas Kellerer]

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

Re: [GENERAL] count distinct and group by

[Szymon Guz]

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;
>>
>> 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: [GENERAL] count distinct and group by

[Andomar]

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 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

Re: [GENERAL] count distinct and group by

[Geoff Winkless]

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 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: [GENERAL] count distinct and group by

[Magnus Hagander]

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 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/

[GENERAL] count distinct and group by

[Szymon Guz]

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: [GENERAL] count distinct slow?

[Tom Lane]

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 good choice, with only 6192 distinct values (hence
just that many hash table entries) in 7495551 input rows.  However,
COUNT(DISTINCT), or any other aggregate with a DISTINCT tag, uses
sort-then-remove-adjacent-duplicates logic for DISTINCT.  That's
evidently a good deal slower for your data set; most likely the data
doesn't fit in your work_mem setting so the sort spills to disk.

The reason DISTINCT aggregates don't consider hash aggregation is
partly lack of round tuits but mostly that an aggregate needs to
execute in a fairly limited amount of memory, and we can't be sure
that the hash table wouldn't get unreasonably large.

regards, tom lane


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

[GENERAL] count distinct slow?

[Roger Pack]

Hello.

As a note, I ran into the following today (doing a select distinct is fast,
doing a count distinct is significantly slower?)

assume a table "issue" with a COLUMN nodename character varying(64);, 7.5M
rows...

select distinct  substring(nodename from 1 for 9) from issue;

-- 5.8s

select count(distinct substring(nodename from 1 for 9)) from issue;

-- 190s


SELECT COUNT(*) FROM (SELECT DISTINCT substring(nodename from 1 for 9) FROM
issue) as temp;

-- 5.5s

I have an index on nodename's substring 1 for 9.

It struck me as odd that a count distinct would be far slower than
selecting distinct rows themselves.  Apparently there are other workarounds
people have come up with as well [1].  Just mentioning in case it's helpful.
Cheers!
-roger-

[1]
http://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow/14732410#14732410

explains:

explain analyze select count(distinct substring(nodename from 1 for 9))
from issue;

Aggregate  (cost=222791.77..222791.78 rows=1 width=16) (actual
time=190641.069..190641.071 rows=1 loops=1)
  ->  Seq Scan on issue  (cost=0.00..185321.51 rows=7494051 width=16)
(actual time=0.016..3487.694 rows=7495551 loops=1)
Total runtime: 190641.182 ms



explain analyze select distinct  substring(nodename from 1 for 9) from
issue;

HashAggregate  (cost=222791.77..222846.45 rows=4375 width=16) (actual
time=6276.578..6278.004 rows=6192 loops=1)
  ->  Seq Scan on issue  (cost=0.00..204056.64 rows=7494051 width=16)
(actual time=0.058..4293.976 rows=7495551 loops=1)
Total runtime: 6278.564 ms


explain analyze SELECT COUNT(*) FROM (SELECT DISTINCT substring(nodename
from 1 for 9) FROM issue) as temp;

Aggregate  (cost=222901.14..222901.15 rows=1 width=0) (actual
time=5195.025..5195.025 rows=1 loops=1)
  ->  HashAggregate  (cost=222791.77..222846.45 rows=4375 width=16) (actual
time=5193.121..5194.454 rows=6192 loops=1)
->  Seq Scan on issue  (cost=0.00..204056.64 rows=7494051 width=16)
(actual time=0.035..3402.834 rows=7495551 loops=1)
Total runtime: 5195.160 ms

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

[Thomas Kellerer]

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
   FROM vtown
   WHERE
   similarity(name, 'Tooneyvara')>  0.1
) t
ORDER BY delta DESC




I like you suggestion, Thomas. It is not that dissimilar from but
cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id)
attempt.
It's also very slightly faster.

Here's another, slightly shorter, variation of your suggestion:

SELECT id, name, delta,
max(rank)  OVER() as cnt
FROM (
  SELECT
id, name, similarity(name, 'Tooneyvara') as delta,
dense_rank() OVER(ORDER BY id) AS rank
FROM vtown
WHERE
similarity(name, 'Tooneyvara')>  0.1
) t
ORDER BY delta DESC


Nice trick with the dense_rank(), never thought of that.

Regards
Thomas



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

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

[Tarlika Elisabeth Schmitz]

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   0.4 4
>> 1700 Ardcroney   0.105   4
>> 1788 Townsfield  0.1 4
>>
>
>This should do it:
>
>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
>   FROM vtown
>   WHERE
>   similarity(name, 'Tooneyvara') > 0.1
>) t
>ORDER BY delta DESC
>


I like you suggestion, Thomas. It is not that dissimilar from but
cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id)
attempt.
It's also very slightly faster.

Here's another, slightly shorter, variation of your suggestion:

SELECT id, name, delta,
   max(rank)  OVER() as cnt
FROM (
 SELECT
   id, name, similarity(name, 'Tooneyvara') as delta,
   dense_rank() OVER(ORDER BY id) AS rank
   FROM vtown
   WHERE
   similarity(name, 'Tooneyvara') > 0.1
) t
ORDER BY delta DESC






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

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

[Thomas Kellerer]

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  delta DESC

produces result:
1787Toomyvara   0.5 4
1787Toomevara   0.4 4
1700Ardcroney   0.105   4
1788Townsfield  0.1 4

What I would like is a "3" in the cnt column (ignoring id duplicates).




This should do it:

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
  FROM vtown
  WHERE
  similarity(name, 'Tooneyvara') > 0.1
) t
ORDER BY delta DESC



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

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

[David Johnston]

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, 'Tooneyvara') > 0.1
>  ORDER BY  delta DESC
> 
> produces result:
> 1787Toomyvara0.54
> 1787Toomevara0.44
> 1700Ardcroney0.1054
> 1788Townsfield0.14
> 
> What I would like is a "3" in the cnt column (ignoring id duplicates).
> 
> 
> How can I achieve this?
> 
> 
> The best I can come up with is:
> 
> SELECT id, name,delta,  count (*) OVER() 
> FROM (
> SELECT DISTINCT ON (id)
>  id, name, similarity(name, 'Tooneyvara') as delta 
>  FROM vtown
>  WHERE
>similarity(name, 'Tooneyvara') > 0.1
>ORDER BY id, delta DESC
> ) AS x
> ORDER by delta DESC 
> 
> -- 
> 
> Best Regards,
> Tarlika Elisabeth Schmitz
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

1.  Write your main query in a WITH (CTE)
2.  Query #1 with appropriate GROUP BY clause (CTE)
3.  In the main statement JOIN 1 and 2

David J.


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

[GENERAL] count (DISTINCT field) OVER ()

[Tarlika Elisabeth Schmitz]

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  delta DESC

produces result:
1787Toomyvara   0.5 4
1787Toomevara   0.4 4
1700Ardcroney   0.105   4
1788Townsfield  0.1 4

What I would like is a "3" in the cnt column (ignoring id duplicates).


How can I achieve this?


The best I can come up with is:

SELECT id, name,delta,  count (*) OVER() 
FROM (
SELECT DISTINCT ON (id)
  id, name, similarity(name, 'Tooneyvara') as delta 
  FROM vtown
  WHERE
similarity(name, 'Tooneyvara') > 0.1
ORDER BY id, delta DESC
) AS x
ORDER by delta DESC 

-- 

Best Regards,
Tarlika Elisabeth Schmitz

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

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

[David Fetter]

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.postgresql.org/docs/8.2/interactive/sql-expressions.html
> > > say that multiple distinct expressions are supported
> > > 
> > > aggregate_name (DISTINCT expression [, expression] )
> 
> > In 8.4, you'll be able to do:
> 
> > WITH d AS (
> > SELECT DISTINCT c1, c2 FROM table1
> > )
> > SELECT count(*) FROM d;
> 
> Nice, but what will be the difference from
> select count(*) from (select distinct c1, c2 from t);
> ?
> Optimisation?

None especially.

> Furthermore... I was actually looking at docs because I needed to
> find a way supported by both postgresql and mysql

Generally, it's *not* a good idea to try to support more than one
back-end.  You wind up maintaining several disparate code bases, all
of which must do exactly the same thing, or you create your own RDBMS
in your client code, or worst of all, some of each.

Unless the most important attribute of the software, i.e. you can
jettison any other feature to support it, is to support more than one
RDBMS back-end, don't even try.  Examples of software which needs to
support multiple RDBMS back-ends include, and are pretty much limited
to, ERD generators and migration tools.

> > and very likely an OLAP version. :)
> 
> What's "an OLAP version" of WITH d AS...

OLAP includes clauses like WINDOW() and OVER(), but since it's not
committed yet, I don't want to get too far into it :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

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

[Ivan Sergio Borgonovo]

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 expressions are supported
> > 
> > aggregate_name (DISTINCT expression [, expression] )

> In 8.4, you'll be able to do:

> WITH d AS (
> SELECT DISTINCT c1, c2 FROM table1
> )
> SELECT count(*) FROM d;

Nice, but what will be the difference from
select count(*) from (select distinct c1, c2 from t);
?
Optimisation?

Furthermore... I was actually looking at docs because I needed to
find a way supported by both postgresql and mysql and I've heard
that mysql is not that good at subselect and I doubt it supports
WITH AS. (OK not really a postgresql problem...).

Meanwhile what would you suggest as a general approach to stuff like

select count(distinct c1, c2) from t;

regardless of mysql support?
and considering mysql support?

I was thinking to find some way to exploit group by, but I didn't
come to anything useful yet.

> and very likely an OLAP version. :)

What's "an OLAP version" of WITH d AS...

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

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

[Pavel Stehule]

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 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])
>>
>> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html
>>
>> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE
>
>
> yeah but no function seems to support
>
> aggregate(distinct x, y)
>
>> CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
>> SFUNC = sfunc,
>> STYPE = state_data_type
>> [ , FINALFUNC = ffunc ]
>> [ , INITCOND = initial_condition ]
>> [ , SORTOP = sort_operator ]
>> )
>
>
> OK... but how am I going to implement an user defined aggregate that
> support without resorting to C?
>
> myaggfunc(distinct x, y)?
>
> Otherwise to what is it referring
>
> http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html
>
> aggregate_name (DISTINCT expression [ , ... ] )
>

ok, I tested and it isn't supported yet. This is documentation bug.
DISTINCT is allowed only for single argument aggregate.

Regards
Pavel Stehule

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

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

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

[David Fetter]

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 [, expression] )

In 8.4, you'll be able to do:

WITH d AS (
SELECT DISTINCT c1, c2 FROM table1
)
SELECT count(*) FROM d;

and very likely an OLAP version. :)

Cheers,
David.
> 
> While previous docs just listed one:
> 
> aggregate_name (DISTINCT expression)
> 
> Still I'm using 8.3 and
> 
> select count(distinct c1, c2) from table1;
> 
> report:
> 
> No function matches the given name and argument types. You might
> need to add explicit type casts.
> 
> What should I write in spite of?
> 
> select count(distinct c1, c2) from table1;
> 
> -- 
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

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

[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 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])
> 
> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html
> 
> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE


yeah but no function seems to support

aggregate(distinct x, y)

> CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
> SFUNC = sfunc,
> STYPE = state_data_type
> [ , FINALFUNC = ffunc ]
> [ , INITCOND = initial_condition ]
> [ , SORTOP = sort_operator ]
> )


OK... but how am I going to implement an user defined aggregate that
support without resorting to C?

myaggfunc(distinct x, y)?

Otherwise to what is it referring

http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html

aggregate_name (DISTINCT expression [ , ... ] )

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

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

[Pavel Stehule]

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.html
> seems to support
> aggregate(distinct exp [,exp])

http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html

http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE


>
> Does the change reflect the change in the possibility to write user
> defined aggregates that support more then one distinct expression?

CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
SFUNC = sfunc,
STYPE = state_data_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
[ , SORTOP = sort_operator ]
)

you are able to write multi param aggregates.

regards
Pavel Stehule


>
> The first thing that comes to my mind to emulate
> count(distinct a,b)
> would be to
>
> create table test.dist (a int, b int);
> insert into test.dist values(1,0);
> insert into test.dist values(1,0);
> insert into test.dist values(1,1);
> insert into test.dist values(0,0);
> select count(*) from (select distinct a,b from test.dist ) a;
>
> but still I can't think of anything that would work with
> aggregate(distinct a,b)
> not just count.
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

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

[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.html
seems to support
aggregate(distinct exp [,exp])

Does the change reflect the change in the possibility to write user
defined aggregates that support more then one distinct expression?

The first thing that comes to my mind to emulate
count(distinct a,b)
would be to

create table test.dist (a int, b int);
insert into test.dist values(1,0);
insert into test.dist values(1,0);
insert into test.dist values(1,1);
insert into test.dist values(0,0);
select count(*) from (select distinct a,b from test.dist ) a;

but still I can't think of anything that would work with
aggregate(distinct a,b)
not just count.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

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

[Pavel Stehule]

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 matches the given name and argument types. You
might need to add explicit type casts.
postgres=# select count(distinct (a,b)) from fooa;
 count
---
 1
(1 row)

regards
Pavel Stehule

2008/12/26 Ivan Sergio Borgonovo :
> 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 [, expression] )
>
> While previous docs just listed one:
>
> aggregate_name (DISTINCT expression)
>
> Still I'm using 8.3 and
>
> select count(distinct c1, c2) from table1;
>
> report:
>
> No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> What should I write in spite of?
>
> select count(distinct c1, c2) from table1;
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

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

[Ivan Sergio Borgonovo]

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 [, expression] )

While previous docs just listed one:

aggregate_name (DISTINCT expression)

Still I'm using 8.3 and

select count(distinct c1, c2) from table1;

report:

No function matches the given name and argument types. You might
need to add explicit type casts.

What should I write in spite of?

select count(distinct c1, c2) from table1;

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Re: FW: [GENERAL] Count & Distinct

[Nils Zonneveld]

anuj wrote:
> 
> > >>> select count(distinct(row)) from table;
> hi,
> I have also tried but I am geting an error.
>

It's true that you get an error from trying to execute the query above;
'distinct' is not a function but a keyword, count() is a agregate
function. Following this the correct notation for your query would be:

select count(distinct ) from ;

I tested this on postgreSQL 7.02 and it works.

I tried this:


NAW=# \d tmpTable
  Table "tmptable"
 Attribute |Type | Modifier
---+-+--
 id| integer |
 name  | varchar(25) |


NAW=# select * from tmpTable;
 id |   name
+---
  1 | Jansen
  2 | Jansen
  3 | Pietersen
  4 | Pietersen
(4 rows)


NAW=# select count(name) from tmpTable;
 count
---
 4
(1 row)


NAW=# select count(distinct name) from tmpTable;
 count
---
 2
(1 row)


Success and kind regards,

Nils Zonneveld
-- 
"Misschien is niets geheel waar, en zelfs dat niet"
Multatuli (Eduard Douwes Dekker) - Idee 1

Re: [GENERAL] Count & Distinct

[J. Atwood]

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]>
> Subject: Re: FW: [GENERAL] Count & Distinct
> 
> I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
> may be doing wrong?
> 
> - Original Message -
> From: "Ross J. Reedstrom" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, August 24, 2000 8:44 AM
> Subject: Re: FW: [GENERAL] Count & Distinct
> 
> 
>> On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
>>> I find that if I create the table described in the email, in psql I
> recieve
>>> 'ERROR:  Attribute 'row' not found'...
>>> 
>>> But this isn't exactly 'ERROR:  parser: parse error at or near
>>> "distinct"'...
>> 
>> Right, that's the error you get from doing this in 6.5.x. The original
>> poster has already been advised to upgrade to 7.0.2.
>> 
>> Ross
>> 
>> --
>> Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
>> NSBRI Research Scientist/Programmer
>> Computer and Information Technology Institute
>> Rice University, 6100 S. Main St.,  Houston, TX 77005
> 
> 

Re: FW: [GENERAL] Count & Distinct

[Stephan Szabo]

Yeah,
There is no column 'row'.  You're trying to use 'row' as
the value expression. Postgres figures it's a column
reference and tries to find it (and doesn't).

 ::=
 
   []  

Set function type is COUNT, set quantifier is DISTINCT,
'row' is not a valid value expression in this case.
Note also, if I'm reading the spec right, * is not
valid in this context either.

On Thu, 24 Aug 2000, Ryan Williams wrote:

> I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
> may be doing wrong?

Re: FW: [GENERAL] Count & Distinct

[Ryan Williams]

I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
may be doing wrong?

- Original Message -
From: "Ross J. Reedstrom" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 24, 2000 8:44 AM
Subject: Re: FW: [GENERAL] Count & Distinct


> On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
> > I find that if I create the table described in the email, in psql I
recieve
> > 'ERROR:  Attribute 'row' not found'...
> >
> > But this isn't exactly 'ERROR:  parser: parse error at or near
> > "distinct"'...
>
> Right, that's the error you get from doing this in 6.5.x. The original
> poster has already been advised to upgrade to 7.0.2.
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: FW: [GENERAL] Count & Distinct

[Ross J. Reedstrom]

On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
> I find that if I create the table described in the email, in psql I recieve
> 'ERROR:  Attribute 'row' not found'...
> 
> But this isn't exactly 'ERROR:  parser: parse error at or near
> "distinct"'...

Right, that's the error you get from doing this in 6.5.x. The original
poster has already been advised to upgrade to 7.0.2.

Ross

-- 
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: FW: [GENERAL] Count & Distinct

[Ryan Williams]

I find that if I create the table described in the email, in psql I recieve
'ERROR:  Attribute 'row' not found'...

But this isn't exactly 'ERROR:  parser: parse error at or near
"distinct"'...


- Original Message -
From: "John McKown" <[EMAIL PROTECTED]>
To: "anuj" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, August 24, 2000 4:55 AM
Subject: Re: FW: [GENERAL] Count & Distinct


> I'm confused, your SELECT uses the variable "row", but there is no such
> variable in your table?
>
> John
>
> On Thu, 24 Aug 2000, anuj wrote:
>
> >
> >
> > > >>> select count(distinct(row)) from table;
> > hi,
> > I have also tried but I am geting an error.
> >
> > ***
> > tempdb=> \d tbltemp
> > Table= tbltemp
> >
+--+--+-
> > --+
> > |  Field   |  Type|
> > Length|
> >
+--+--+-
> > --+
> > | id   | int4 |
> > 4 |
> > | name | varchar()|
> > 40 |
> >
+--+--+-
> > --+
> > tempdb=> select count(distinct(row)) from tbltemp;
> > ERROR:  parser: parse error at or near "distinct"
> > tempdb=>
> > ***
> >
> > Thanks
> > Anuj
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> > Behalf Of [EMAIL PROTECTED]
> > Sent: Wednesday, August 23, 2000 9:12 AM
> > To: J. Atwood
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: [GENERAL] Count & Distinct
> >
> >
> > It works fine. Please try again
> >
> >
> >
> >
> > On Wed, 23 Aug 2000, J. Atwood wrote:
> >
> > > Search a lot of archives and have come up with very little (much old)
> > >
> > > obviously
> > >
> > > >>> select count(distinct(row)) from table;
> > >
> > > Does not work so what is the work around? I have tried a bunch of
stuff.
> > >
> > > Thanks,
> > > J
> > >
> > >
> >
> >
>

Re: FW: [GENERAL] Count & Distinct

[John McKown]

I'm confused, your SELECT uses the variable "row", but there is no such
variable in your table?

John

On Thu, 24 Aug 2000, anuj wrote:

> 
> 
> > >>> select count(distinct(row)) from table;
> hi,
> I have also tried but I am geting an error.
> 
> ***
> tempdb=> \d tbltemp
> Table= tbltemp
> +--+--+-
> --+
> |  Field   |  Type|
> Length|
> +--+--+-
> --+
> | id   | int4 |
> 4 |
> | name | varchar()|
> 40 |
> +--+--+-
> --+
> tempdb=> select count(distinct(row)) from tbltemp;
> ERROR:  parser: parse error at or near "distinct"
> tempdb=>
> ***
> 
> Thanks
> Anuj
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of [EMAIL PROTECTED]
> Sent: Wednesday, August 23, 2000 9:12 AM
> To: J. Atwood
> Cc: [EMAIL PROTECTED]
> Subject: Re: [GENERAL] Count & Distinct
> 
> 
> It works fine. Please try again
> 
> 
> 
> 
> On Wed, 23 Aug 2000, J. Atwood wrote:
> 
> > Search a lot of archives and have come up with very little (much old)
> >
> > obviously
> >
> > >>> select count(distinct(row)) from table;
> >
> > Does not work so what is the work around? I have tried a bunch of stuff.
> >
> > Thanks,
> > J
> >
> >
> 
> 

Re: [GENERAL] COUNT DISTINCT

[Jeff Waugh]

> 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 Australia - http://linux.org.au/installfest/

Re: [GENERAL] count & distinct

[Vashenko Maxim]

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 calculated). I've tried to define
> a view, but it didn't worked (
> create view distValues as select distinct ...
> but views doesn't support distinct clause)...
> 
> Well, maybe I should create a new aggregate
> function, but before spending time on PGSQL
> guide I would like to know if someone can
> give me a fast tip... ;-)
> Thanks in advance
> Andrea
> 
> PS: well, of course I can open a cursor on
> a "select distinct column from table" and then
> loop over the cursor couting how many values
> there are, but it seem a bit ugly...


SELECT field, count(*) FROM table GROUP BY field



With best regards, Max Vaschenko,
Nizhny Novgorod Information Networks.

RE: [GENERAL] count & distinct

[Andrew Snow]

> 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 tried to define
> a view, but it didn't worked ( 
> create view distValues as select distinct ...
> but views doesn't support distinct clause)...


SELECT COUNT(DISTINCT field_name) FROM table_name;

Works for me in v7.


Regards,
Andrew Snow
[EMAIL PROTECTED]
 

[GENERAL] count & distinct

[Andrea Aime]

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 tried to define
a view, but it didn't worked ( 
create view distValues as select distinct ...
but views doesn't support distinct clause)...

Well, maybe I should create a new aggregate
function, but before spending time on PGSQL
guide I would like to know if someone can
give me a fast tip... ;-)
Thanks in advance
Andrea

PS: well, of course I can open a cursor on
a "select distinct column from table" and then
loop over the cursor couting how many values
there are, but it seem a bit ugly...