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
