,请问如何可以用一条语句查询失败率
表test结构如下,其中status=1表示成功,status=0表示失败,请问如何可以用一条语句查询到每个interface_name的失败率
-
SQL code
+-----------------+--------+-------+-------------+--------------+--------------+--------------+-----------+---------------+---------------+ | interface_name | status | count | avg_latency | slow_count_1 | slow_count_2 | slow_count_3 | client_id | ip | timestamp | +-----------------+--------+-------+-------------+--------------+--------------+--------------+-----------+---------------+---------------+ | /email/exist | 1 | 1 | 246 | 1 | 1 | 0 | 111111 | 127.0.0.1 | 1349689181769 | | hasProfileUser | 0 | 100 | 50 | 30 | 20 | 10 | 111111 | 192.168.10.22 | 1234567 | | hasProfileUser | 1 | 100 | 50 | 30 | 20 | 10 | 111111 | 192.168.10.22 | 1234567 | | hasProfileUser1 | 1 | 100 | 50 | 30 | 20 | 10 | 111111 | 192.168.10.22 | 1234567 | | hasProfileUser2 | 1 | 100 | 50 | 30 | 20 | 10 | 111111 | 192.168.10.22 | 1234567 | +-----------------+--------+-------+-------------+--------------+--------------+--------------+-----------+---------------+---------------+
select interface_name,sum(if(status=0,1,0))/count(*)
from test group by interface_name
if(status=0,1,0):如果status=0,取值1,
sum:计算出1的总数,即status=0的总数
sum(if(status=0,1,0))换成count(if(status=0,1,0))一样
如果status=0,返回1,否则返回0