From: Hank Eskin
Date: March 13 1999 1:35pm
Subject: Select Count(Distinct <fieldname>) from <table>...
List-Archive: http://lists.mysql.com/mysql/157
Message-Id: <36EA6983.F1B458CB@ix.netcom.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


There were recently two identical questions posted,
with two identical answers, neither of which do what
is needed. I have the same need, but have not found a 
single query to do this.

I need to do:
 SELECT COUNT(DISTINCT <fieldname>) from <tablename> ;
or
 SELECT COUNT(DISTINCT <fieldname1>,<fieldname2>,<fieldname3>) from <tablename>
;

In other words, I want ONE record returned, with the total COUNT
of DISTINCT values in the table.

The proposed (incorrect) solutions were both:
  SELECT count(*) FROM <tablename> GROUP BY <fieldname>;

This just returns the count of records for each distinct <fieldname>,
which is identical to 
  SELECT <fieldname>, count(*) from <tablename> GROUP BY <fieldname>;
just without the key field in the output - one record for each 
distinct <fieldname> in the table.

The only solution I have used to do this is issue:
  SELECT <fieldname>, count(*) from <tablename> GROUP BY <fieldname>;
and then get the mysql_NUMROWS (using PHP3).

 So, is there a way, in one simple query to return this value?

 Thanks,
 -Hank

p.s. Oracle handles this very well... ;)