X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F3DF93A436B for ; Wed, 17 Nov 2004 19:27:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86694-04 for ; Wed, 17 Nov 2004 19:27:21 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id D90A13A43B2 for ; Wed, 17 Nov 2004 19:27:20 +0000 (GMT) Received: (qmail 13912 invoked from network); 17 Nov 2004 19:27:20 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.22?) (64.3.242.80) by 0 with SMTP; 17 Nov 2004 19:27:20 -0000 In-Reply-To: <20041117064813.V17486@megazone.bigpanda.com> References: <1B2F7B00-3808-11D9-95C2-000D93AE0944@sitening.com> <20041116115807.E79990@megazone.bigpanda.com> <6E79C2FE-380B-11D9-95C2-000D93AE0944@sitening.com> <20041116141911.K84773@megazone.bigpanda.com> <20041117064813.V17486@megazone.bigpanda.com> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: Content-Transfer-Encoding: 7bit Cc: PgSQL - SQL From: Thomas F.O'Connell Subject: Re: Counting Distinct Records Date: Wed, 17 Nov 2004 13:27:17 -0600 To: Stephan Szabo X-Mailer: Apple Mail (2.619) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/178 X-Sequence-Number: 19497 The specific problem I'm trying to solve involves a user table with some history. Something like this: create table user_history ( user_id int event_time_stamp timestamp ); I'd like to be able to count the distinct user_ids in this table, even if it were joined to other tables. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 17, 2004, at 8:52 AM, Stephan Szabo wrote: > On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: > >> Hmm. I was more interested in using COUNT( * ) than DISTINCT *. >> >> I want a count of all rows, but I want to be able to specify which >> columns are distinct. > > I'm now a bit confused about exactly what you're looking for in the > end. > Can you give a short example? > >> That's definitely an interesting approach, but testing doesn't show it >> to be appreciably faster. >> >> If I do a DISTINCT *, postgres will attempt to guarantee that there >> are >> no duplicate values across all columns rather than a subset of >> columns? >> Is that right? > > It guarantees one output row for each distinct set of column values > across > all columns.