I've got the following query that produces a large table for me.
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 BETWEEN '40000000' AND '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 BETWEEN '599000000' AND '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 works just fine although I'm certain there's got to be
a more efficient way of doing the same thing. I'm relatively new to MySQL
so I took the brute force approach.
My problem is that I want to produce totals of each of the columns and can't
figure out how to do it. Any suggestions on how I can do this?
Thanks,
Jack
Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705
