count function alternative in postgres

junaid malik <junaidmalik14@gmail.com> writes:

Is there any alternative of mysql function COUNT(DISTINCT expr,
[expr...]) in postgres. We get error if we

The SQL-standard way to do that would be

select count(*) from (select distinct expr,expr,... from ...) as ss;

COUNT with multiple arguments is not anywhere in the standard.

regards, tom lane

junaid malik wrote:

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

I already answered your question on dbforums.com. But in Postgres you
can do:

SELECT COUNT( DISTINCT row(col1, col2, col3) ) FROM foo
or
SELECT COUNT( DISTINCT (col1, col2, col3) ) FROM foo

On Sat, Apr 3, 2010 at 3:02 PM, Scott Bailey <artacus@comcast.net> wrote:

junaid malik wrote:

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

I already answered your question on dbforums.com. But in Postgres you can
do:

SELECT COUNT( DISTINCT row(col1, col2, col3) ) FROM foo
or
SELECT COUNT( DISTINCT (col1, col2, col3) ) FROM foo

very clever! This is similar to how I use rowtypes to get around the
single column restrictions on function calls in the select field list.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

On Sat, Apr 3, 2010 at 3:02 PM, Scott Bailey <artacus@comcast.net> wrote:

SELECT COUNT( DISTINCT row(col1, col2, col3) ) FROM foo

very clever! This is similar to how I use rowtypes to get around the
single column restrictions on function calls in the select field list.

Cute, but note it will only work in 8.4 or later ...

regards, tom lane