SQL获取总计不良记录的百分比

i am relatively new to SQL. Each employee access an account for testing with a tech, sometimes it's a good attempt, sometimes it's bad, so I need to calculate the percentage of the bad attempts mostly, my report should look something like this:

我对SQL比较陌生。每个员工都使用技术访问帐户进行测试,有时这是一个很好的尝试,有时候很糟糕,所以我需要计算大部分不良尝试的百分比,我的报告看起来应该是这样的:

SELECT employee, event, total, percentage FROM my_table

从my__table中选择员工,事件,总数,百分比

employee |  event |  total |  percentage|
user1    |  good  |   50   |    50%     |
user1    |  bad   |   50   |    50%     |

2 个解决方案

#1

Calculate the total in a subquery and then JOIN to calculate percentage on each row.

计算子查询中的总计,然后加入JOIN以计算每行的百分比。

SELECT employee, event, COUNT(*),  COUNT(*) * 100.0 / t.total as percentage
FROM my_table
JOIN (SELECT employee, count(*) total
      FROM my_table
      GROUP BY employee) T
  ON my_table.employee = t.employee
GROUP BY employee, event

#2

Try something like this calculate the bad event percentage for each employee

尝试这样的事情来计算每个员工的不良事件百分比

select employee,(sum(case when event = 'bad' then 1 else 0 end) / count(*)) * 100
From Yourtable
Group by employee

#1