Thread: 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-tp28126794p28126794.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

junaidmalik14 <junaidmalik14@gmail.com> 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)
Is that semantically different from this SQL standard syntax?:
SELECT profile.id, profile.name, profile.age, COUNT(*) FROM profile GROUP BY profile.id, profile.name, profile.age;

-Kevin

On Sat, Apr 3, 2010 at 8:59 AM, junaidmalik14 <junaidmalik14@gmail.com> 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

You can do:

SELECT COUNT(*) FROM (SELECT DISTINCT profile.id, profile.name,
profile.age FROM ...) x;

...Robert

Robert Haas <robertmhaas@gmail.com> wrote:
> You can do:
> 
> SELECT COUNT(*) FROM (SELECT DISTINCT profile.id, profile.name,
> profile.age FROM ...) x;
Ah, I see what they wanted now.  In older versions of PostgreSQL,
they might get better performance in some cases by using GROUP BY:
SELECT COUNT(*) FROM (SELECT id, name, age FROM profile GROUP BY id, name, age) x;
I don't remember offhand what version started considering a hash for
DISTINCT.
-Kevin

2010/4/5 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Robert Haas <robertmhaas@gmail.com> wrote:
>
>> You can do:
>>
>> SELECT COUNT(*) FROM (SELECT DISTINCT profile.id, profile.name,
>> profile.age FROM ...) x;
>
> Ah, I see what they wanted now.  In older versions of PostgreSQL,
> they might get better performance in some cases by using GROUP BY:
>
> SELECT COUNT(*) FROM
>  (SELECT id, name, age FROM profile GROUP BY id, name, age) x;
>
> I don't remember offhand what version started considering a hash for
> DISTINCT.
>
8.4

http://www.postgresql.org/docs/8.4/static/release-8-4.html

regards
Pavel

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