The output looks like this... +----------+-----+------+------+ ~snip~ +-------+ | Router | 64K | 128K | 192K | ~snip~ | Total | +----------+-----+------+------+ ~snip~ +-------+ | router_1 | 0 | 0 | 0 | ~snip~ | 6 | | router_2 | 1 | 6 | 0 | ~snip~ | 70 | | router_3 | 0 | 0 | 0 | ~snip~ | 3 | | router_4 | 0 | 0 | 0 | ~snip~ | 3 | | router_5 | 0 | 0 | 0 | ~snip~ | 1 |
interface.description isn't listed and I'd like it to stay this way. interface.description is usually a unique value. However, when an interface gets moved, a new record gets created with the same interface.description but different interface.id and interface.name values. I'd like to eliminate those semi-duplicate records from the count. I know...I could modify the records when an interface changes but for various reasons I need to keep both the before and after data. Jack -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 01, 2004 1:52 PM To: Jack Coxen Cc: MySQL List (E-mail) Subject: Re: DISTINCT query Jack, I am not sure what you want your output to look like... If you want the same columns (the routerid with the counts of how many interfaces operate at which speed) but broken down by unique descriptions that means to me that you have multiple descriptions for each unique interface ID... Is this the case? If it isn't then your existing GROUP BY will already make your report unique by interface.comment (because there is only 1 comment per ID, understand?) If you could provide a prototype header to the report you want to make I will try my best to help you work it out. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jack Coxen <[EMAIL PROTECTED] To: "MySQL List (E-mail)" <[EMAIL PROTECTED]> ve.com> cc: Fax to: 06/01/2004 01:12 Subject: DISTINCT query PM I'm having trouble with a query and hope someone of the list can help. I'm running version 4.0.16. I'm trying to query against two tables: mysql> DESCRIBE router; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | rid | int(11) unsigned | | PRI | NULL | auto_increment | | name | char(120) | | | | | | pop | char(10) | | | | | | popid | tinyint(3) unsigned | | | 0 | | +-------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.04 sec) mysql> DESCRIBE interface; +-------------+---------------------------+------+-----+---------+---------- ------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------------+------+-----+---------+---------- ------+ | id | int(11) unsigned | | PRI | NULL | auto_increment | | name | char(255) | | | | | | rid | int(11) | | | 0 | | | speed | bigint(11) | YES | | NULL | | | description | char(255) | YES | | NULL | | | status | enum('active','inactive') | YES | | active | | +-------------+---------------------------+------+-----+---------+---------- ------+ 6 rows in set (0.02 sec) using the following query: SELECT router.name AS Router, SUM(IF(speed<='64000',1,0)) AS 64K, SUM(IF(speed='128000',1,0)) AS 128K, SUM(IF(speed='192000',1,0)) AS 192K, SUM(IF(speed='256000',1,0)) AS 256K, SUM(IF(speed='384000',1,0)) AS 384K, SUM(IF(speed='512000',1,0)) AS 512K, SUM(IF(speed='768000',1,0)) AS 768K, SUM(IF(speed='1024000',1,0)) AS 1M, SUM(IF(speed='1152000',1,0)) AS 1152K, SUM(IF(speed='1280000',1,0)) AS 1280K, SUM(IF(speed='1536000' OR speed='1544000',1,0)) AS 'V/T1', SUM(IF(speed='3000000',1,0)) AS 3M, SUM(IF(speed='6000000',1,0)) AS 6M, SUM(IF(speed='9000000',1,0)) AS 9M, SUM(IF(speed='10000000',1,0)) AS 10M, SUM(IF(speed='12000000',1,0)) AS 12M, SUM(IF(speed='24000000',1,0)) AS 24M, SUM(IF(speed>'40000000' AND speed<'46000000',1,0)) AS 'DS3/T3', SUM(IF(speed='100000000',1,0)) AS 100M, SUM(IF(speed='155000000',1,0)) AS OC3, SUM(IF(speed>'599000000' AND speed<'650000000',1,0)) AS OC12, SUM(IF(speed='1000000000',1,0)) AS Gigabit, SUM(IF(speed='115000',1,0)) AS Dialup, SUM(IF(speed>'64000' AND speed!='128000' AND speed!='192000' AND speed!='256000' AND speed!='384000' AND speed!='512000' AND speed!='768000' AND speed!='1024000' AND speed!='1152000' AND speed!='1280000' AND speed!='1536000' AND speed!='1544000' AND speed!='3000000' AND speed!='6000000' AND speed!='9000000' AND speed!='10000000' AND speed!='12000000' AND speed!='24000000' AND speed NOT BETWEEN '40000000' AND '46000000' AND speed!='100000000' AND speed!='155000000' AND speed NOT BETWEEN '599000000' AND '650000000' AND speed!='1000000000' AND speed!='115000',1,0)) AS Other, COUNT(*) AS Total FROM router INNER JOIN interface USING (rid) GROUP BY router.rid ORDER BY router.name; The query as written runs fine. My problem is that I want to SELECT only DISTINCT values for interface.description and not display the descriptions in the result. Is this possible? And if so, how do I do it? Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705
