List:General Discussion« Previous MessageNext Message »
From:Mervyn Chapman Date:February 21 2003 5:50am
Subject:Re: How do I use COUNT() and DISTINCT together?
View as plain text  
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" <daevid@stripped>
To: <mysql@stripped>
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 <mysql-thread133011@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-redlogic=intergate.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

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