How do you run arithmetic on values in one column based on the values of a different column and grou-CodePudding

I have a MySQL table that looks like this:

time            measure_name    measure_value::bigint
12/1/2021 0:00  volume          791
12/1/2021 0:00  successVolume   791
12/1/2021 0:01  volume          1261
12/1/2021 0:01  successVolume   1261
12/1/2021 0:02  volume          1332
12/1/2021 0:02  successVolume   1332
12/1/2021 0:03  volume          1145
12/1/2021 0:03  successVolume   1145

What I'd like to do is get the success rate by time: (successVolume/volume)*100

time            successRate
12/1/2021 0:00  100.0
12/1/2021 0:01  100.0

I've searched through many posts but haven't quite found one that helped. I'm sure I'm just not searching using the correct question.

CodePudding user response：

You can join both values from two subselects

But you should save dates in MySQL only in the tiome format that MySQL uses yyyy-mm-dd hh:MM:ss

Also you should add a index on (time,measure_name )

SELECT a.`time`, (a.`measure_value` / b.`measure_value`) * 100 as successRate
FROM
(SELECT `time`, `measure_name`, `measure_value` FROM timemeasure WHERE  `measure_name` = 'volume' ) a
INNER JOIN (SELECT `time`, `measure_name`, `measure_value` FROM timemeasure  WHERE `measure_name` = 'successVolume' ) b
ON a.`time` = b.`time`

time           | successRate
:------------- | ----------:
12/1/2021 0:00 |    100.0000
12/1/2021 0:01 |    100.0000
12/1/2021 0:02 |    100.0000
12/1/2021 0:03 |    100.0000

db<>fiddle here