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