Обсуждение: count function alternative in postgres
Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct Thanks -- View this message in context: http://old.nabble.com/count-function-alternative-in-postgres-tp28126792p28126792.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
junaidmalik14 wrote on 03.04.2010 14:58: > > Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in > postgres. We get error if we > > write count like this count(distinct profile.id, profile.name, profile.age) > but it works well in mysql. > > Reference url is given below > > http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct > > Thanks Your question has been answered several times already (including this list) http://archives.postgresql.org/pgsql-hackers/2010-04/msg00182.php http://archives.postgresql.org/pgsql-hackers/2010-04/msg00179.php http://forums.devshed.com/postgresql-help-21/count-function-alternative-in-postgres-691450.html http://www.dbforums.com/postgresql/1655165-count-function-alternative-postgres.html
Quoth junaidmalik14@gmail.com (junaidmalik14): > > Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in > postgres. We get error if we > > write count like this count(distinct profile.id, profile.name, profile.age) > but it works well in mysql. Pg does support COUNT(DISTINCT ), but only for a single column. The best I can come up with for multiple columns is select count(distinct profile.tuple) from (select (id, name, age) as tuple from profile) as profile; or alternatively select count(*) from (select distinct (id, name, age) as tuple from profile) as profile; Ben
Thanks tkellerer. Junaid tkellerer wrote: > > junaidmalik14 wrote on 03.04.2010 14:58: >> >> Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) >> in >> postgres. We get error if we >> >> write count like this count(distinct profile.id, profile.name, >> profile.age) >> but it works well in mysql. >> >> Reference url is given below >> >> http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct >> >> Thanks > > Your question has been answered several times already (including this > list) > > http://archives.postgresql.org/pgsql-hackers/2010-04/msg00182.php > http://archives.postgresql.org/pgsql-hackers/2010-04/msg00179.php > http://forums.devshed.com/postgresql-help-21/count-function-alternative-in-postgres-691450.html > http://www.dbforums.com/postgresql/1655165-count-function-alternative-postgres.html > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- View this message in context: http://old.nabble.com/count-function-alternative-in-postgres-tp28126792p28339789.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Thanks Ben. It works fine. Junaid. Ben Morrow-2 wrote: > > Quoth junaidmalik14@gmail.com (junaidmalik14): >> >> Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) >> in >> postgres. We get error if we >> >> write count like this count(distinct profile.id, profile.name, >> profile.age) >> but it works well in mysql. > > Pg does support COUNT(DISTINCT ), but only for a single column. The best > I can come up with for multiple columns is > > select count(distinct profile.tuple) from > (select (id, name, age) as tuple from profile) > as profile; > > or alternatively > > select count(*) from > (select distinct (id, name, age) as tuple from profile) > as profile; > > Ben > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- View this message in context: http://old.nabble.com/count-function-alternative-in-postgres-tp28126792p28339793.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.