Обсуждение: 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

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