From: Michael Widenius
Date: March 14 1999 12:17pm
Subject: Re: select count distinct
List-Archive: http://lists.mysql.com/mysql/222
Message-Id: <14059.43023.471333.309953@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Scott" == Scott Liu <scott@stripped> writes:

Scott> ChinaConnect wrote:

>> Hi,
>> In mysql, it doesn't look like you can do this:
>> select count(distinct id) from sometable.
>> 
>> If this is true, how would I count the number of times distinct ids occur?
>> 
>> 

Scott> I noticed that, too. What I did is to "select id from sometable" and then
Scott> count the
Scott> distinct id in the application program. Can anyone give us a better way to do
Scott> this?

Scott> Scott

Until MySQL 3.23:

CREATE TABLE tmp_table_$$ (distinct_value CHAR(x), PRIMARY KEY (distinct_value));
INSERT IGNORE INTO tmp_table SELECT distinct_value FROM table_name;
SELECT count(*) from tmp_table_$$;
drop table tmp_table_$$;

Regards,
Monty