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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
