From: Daevid Vincent
Date: February 21 2003 5:03am
Subject: How do I use COUNT() and DISTINCT together?
List-Archive: http://lists.mysql.com/mysql/133011
Message-Id: <000001c2d966$9355a3b0$2101a8c0@Locutus>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Given this table, I want to count the number of distinct targetranges.

CREATE TABLE job (
  job_id int(10) unsigned NOT NULL auto_increment,
  customer_id int(10) unsigned NOT NULL default '0',
  scanner_id int(10) unsigned NOT NULL default '0',
  status_id int(10) unsigned NOT NULL default '0',
  changed timestamp(14) NOT NULL,
  targetrange char(255) default NULL,
  force_exit tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (job_id),
  KEY status_id (status_id)
) TYPE=InnoDB;

I tried "select distinct targetrange from job;" and I do get "273" rows.
But I would have to use PHP's mysql_num_rows() to get that. I don't need
all the rows returned however, just the count(), so I think this method
is wasteful and probably will get slower as the db fills up with
millions of rows.

mysql> select count(1) from job;                      
+----------+
| count(1) |
+----------+
|      351 |
+----------+

Is more of what I want, but how do I combine that with DISTINCT to get
something like:

+----------+
| count(1) |
+----------+
|      273 |
+----------+

And also, should I make targetrange an index too to help speed up the
query or will it not matter?