How to divide values within a single column and store the result in another column in MySQL

Question

I have a table called "alerts" that looks like this.

user_id event   alert_type
1       imp       s
2       imp       b
3       imp       b
3       clk       b
6       imp       b
9       imp       s
9       clk       s
2       clk       b
6       clk       b
17      imp       p
18      imp       p
19      imp       s
11      imp       b
14      imp       s
1       clk       s
11      clk       b
15      imp       p
15      clk       p
20      imp       b
21      imp       b
22      imp       p
23      imp       s
24      imp       s
23      clk       s
18      clk       p
29      imp       b
29      clk       b
16      imp       p
16      clk       p
27      imp       s
28      imp       s

I am trying to create a new column called result for every alert_type where result = clk/imp

My desired output is

alert_type  result
b           0.7143
p           0.6
s           0.375

I am trying to do it this way but I know it is very inefficient.

select 
((select alert_type,count(*) from alerts where alert_type = 'b' and event = 'clk') / 
 (select alert_type,count(*) from alerts where alert_type = 'b' and event = 'imp')) result 
 from dual;

Please help me with a concise way of achieving the desired output.

Answer 1

You can try something like that:

SELECT 
alert_type,
(SUM(`event` = 'clk' ) / SUM(`event` = 'imp')) AS result
FROM alerts
WHERE alert_type ='b'
GROUP BY alert_type

Note:

Since MySQL boolean expression resolves into 0/1 so that you can capitalize this in your use case.

SUM(a=b) returns 1 only if a is equal to b

More: Since I've used SUM instead of COUNT so you don't need be worried about DIVIDE BY ZERO error.