Sharath Sharath - 2 months ago 5x
SQL Question

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

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 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.


You can try something like that:

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


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.