select count(distinct targetrange) from job; The index would help, but with a 255 char field, you'd suffer with disk space.
HTH Mervyn Chapman ----- Original Message ----- From: "Daevid Vincent" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 12:03 AM Subject: How do I use COUNT() and DISTINCT together? > 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? > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
