List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:February 21 2003 5:03am
Subject:How do I use COUNT() and DISTINCT together?
View as plain text  
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? 

Thread
How do I use COUNT() and DISTINCT together?Daevid Vincent21 Feb
  • Re: How do I use COUNT() and DISTINCT together?Zak Greant21 Feb
  • Re: How do I use COUNT() and DISTINCT together?Mervyn Chapman21 Feb