Search code examples
mysqlsqldatabaseselectdatatable

Divide values in one column by values in another one index back in MySQL

I have a table that looks like this:

taken total
NaN 30.02
NaN 68.03
174.0 18.67
NaN 44.50
134.0 68.33

I want divide all the numbers in the taken column by the number in the total column one index back. The resulting column would look like:

taken total kpi
NaN 30.02 NaN
NaN 68.03 NaN
174.0 18.67 2.55
NaN 44.50 NaN
134.0 68.33 3.01

I got the KPI values by dividing 174.0/68.03 and 134.0/44.50, respectively. How can I achieve this with a SELECT statement in MySQL?

Solution 
  • SELECT taken, total, 
  IF(taken = 'NaN', 'NaN', ROUND(taken / lag_total, 2)) AS kpi
FROM (
    SELECT taken, total,
      LAG(total) OVER (ORDER BY user_id, timestamp, order_line_number) AS lag_total
    FROM a_table_that_looks_like_this
) AS t;