List:General Discussion« Previous MessageNext Message »
From:Hank Eskin Date:March 13 1999 1:35pm
Subject:Select Count(Distinct <fieldname>) from <table>...
View as plain text  
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... ;)
Thread
Select Count(Distinct <fieldname>) from <table>...Hank Eskin13 Mar
  • Re: Select Count(Distinct <fieldname>) from <table>...bert hubert13 Mar
  • Re: MySQL 4.0.11 is releasedKen Menzel26 Feb
    • Re: MySQL 4.0.11 is releasedMark Matthews26 Feb
      • Re: MySQL 4.0.11 is releasedLenz Grimmer26 Feb
RE: MySQL 4.0.11 is releasedDuncan Salada26 Feb
  • Re: MySQL 4.0.11 is releasedMark Matthews26 Feb
  • RE: MySQL 4.0.11 is releasedLenz Grimmer26 Feb
    • Re: MySQL 4.0.11 is releasedBenjamin Pflugmann26 Feb
      • Re: MySQL 4.0.11 is releasedLenz Grimmer28 Feb