X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3F8749DCD60 for ; Sat, 10 Dec 2005 21:25:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84150-02 for ; Sat, 10 Dec 2005 21:25:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from viefep20-int.chello.at (viefep12-int.chello.at [213.46.255.25]) by postgresql.org (Postfix) with ESMTP id 878479DD883 for ; Sat, 10 Dec 2005 21:25:17 -0400 (AST) Received: from OTTO ([213.222.166.137]) by viefep20-int.chello.at (InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP id <20051211012520.HICN24926.viefep20-int.chello.at@OTTO>; Sun, 11 Dec 2005 02:25:20 +0100 Message-ID: <007101c5fdf1$c03c7360$0200a8c0@OTTO> From: =?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= To: "Jaime Casanova" Cc: References: <20051209053849.498E8F0BEC@svr2.postgresql.org> <003001c5fded$53a2c5a0$0200a8c0@OTTO> Subject: Re: select count of distinct rows Date: Sun, 11 Dec 2005 02:25:20 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/84 X-Sequence-Number: 23626 Yes, almost. I need the list of all different rows. It's syntax error at the *. ROW(*) ^ Otto ----- Original Message ----- From: "Jaime Casanova" To: "Havasvölgyi Ottó" Cc: Sent: Sunday, December 11, 2005 2:16 AM Subject: Re: select count of distinct rows On 12/10/05, Havasvölgyi Ottó wrote: > Hi, > > I would like to select the count of distinct rows in a table. > > SELECT COUNT(DISTINCT *) FROM mytable; > > This does not work. How can I do it with Postgres? > > Thanks, > Otto > I guess what you need is to know how many times a single row is duplicated so i think what you need is something like this: SELECT fld1, COUNT(DISTINCT fld1) FROM (SELECT ROW(*) as fld1 FROM mytable) AS foo GROUP BY fld1; -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)