Hi David,
Use:
SELECT COUNT(DISTINCT targetrange) FROM job;
Cheers!
On Thu, Feb 20, 2003 at 09:03:29PM -0800, Daevid Vincent wrote:
> 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 <mysql-thread133011@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-zak=mysql.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Zak Greant <zak@stripped>
MySQL AB Community Advocate
Personal Blog: http://zak.fooassociates.com
Using and Managing MySQL
MySQL Training: Washington DC, March 17-21, 2003
Visit http://mysql.com/training for more information
