In-Reply-To: <20041116115807.E79990@megazone.bigpanda.com>
References: <1B2F7B00-3808-11D9-95C2-000D93AE0944@sitening.com>
	<20041116115807.E79990@megazone.bigpanda.com>
Mime-Version: 1.0 (Apple Message framework v619)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <6E79C2FE-380B-11D9-95C2-000D93AE0944@sitening.com>
Content-Transfer-Encoding: 7bit
Cc: PgSQL - SQL <pgsql-sql@postgresql.org>
From: Thomas F.O'Connell <tfo@sitening.com>
Subject: Re: Counting Distinct Records
Date: Tue, 16 Nov 2004 14:09:33 -0600
To: Stephan Szabo <sszabo@megazone.bigpanda.com>

Is there another way to accomplish what the former is doing, then?

For practical reasons, I'd like to come up with something better.

For theoretical curiosity, I'd like to know whether there's a way to 
combine COUNT and DISTINCT that still allows one to reference * rather 
than naming specific columns without grouping.

If I resort to GROUP BY, is there an efficient way of counting all the 
groups, or would it just be something like:

SELECT COUNT ( * ) FROM ( SELECT ... GROUP BY ... );

-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 16, 2004, at 2:03 PM, Stephan Szabo wrote:

> On Tue, 16 Nov 2004, Thomas F.O'Connell wrote:
>
>> I am wondering whether the following two forms of SELECT statements 
>> are
>> logically equivalent:
>>
>> SELECT COUNT( DISTINCT table.column ) ...
>>
>> and
>>
>> SELECT DISTINCT COUNT( * ) ...
>
> Not in general.
>
> The former counts how many distinct table.column values there are.  The
> distinct in the latter would be basically meaningless unless there's a
> group by involved.
>