求使用率，sql语句。解决办法_Oracle管理

求使用率，sql语句。
switch单独一个表，有switch_code
switch_port单独一个表，有switch_code,switch_port_code,status.（status有：U（in use），R，D，A四种情况）
如何求出每个switch中port口占用率？

------解决方案--------------------

SQL code

WITH t AS (SELECT 'A' switch_code, 'u' status FROM dualUNION ALLSELECT 'A' , 'r' status FROM dualUNION ALLSELECT 'A' , 'd' status FROM dualUNION ALLSELECT 'A' , 'a' status FROM dualUNION ALLSELECT 'A' , 'd' status FROM dualUNION ALLSELECT 'A' , 'u' status FROM dualUNION ALLSELECT 'A' , 'u' status FROM dualUNION ALLSELECT 'A' , 'u' status FROM dual)SELECT switch_code,       SUM(decode(status,'u',1,0))/COUNT(switch_code) u占有率,       SUM(decode(status,'a',1,0))/COUNT(switch_code) a占有率,       SUM(decode(status,'d',1,0))/COUNT(switch_code) d占有率,       SUM(decode(status,'r',1,0))/COUNT(switch_code) r占有率       FROM t       GROUP BY switch_code--result:A    0.5    0.125    0.25    0.125
------解决方案--------------------
SQL codeselect sum(decode(status,'U',1,0))/count(1)*100from switch_port c group by switch_code