Excel to Mysql Query

Question

Need help to translate excel formula to MySQL query

here is the question =(SUM(D2:D5189))/(SUM(C2:C5189)).

Thanks much for in advance.enter image description here

Answer 1

Given

drop table if exists t;
create table t(dt date,cola int,colb int);
insert into t values
(str_to_date('29/10/2020','%d/%m/%Y'),147,150),
(str_to_date('30/10/2020','%d/%m/%Y'),160,170),
(str_to_date('01/11/2020','%d/%m/%Y'),170,170),
(str_to_date('02/11/2020','%d/%m/%Y'),180,170),
(str_to_date('03/11/2020','%d/%m/%Y'),190,170);

You could use correlated sub queries to get the totals from the current row to the end of column

select t.*,
         (select sum(cola) from t t1 where t1.dt >=t.dt) sumcola,
         (select sum(colb) from t t1 where t1.dt >=t.dt) sumcolb,
         (select sum(colb) from t t1 where t1.dt >=t.dt) /
         (select sum(cola) from t t1 where t1.dt >=t.dt) kpi
from t;

Or if you have version 8 or above

select t.*,
         sum(cola) over (order by dt rows between current row and unbounded following) sumcola,
         sum(colb) over (order by dt rows between current row and unbounded following) sumcolb,
         sum(colb) over (order by dt rows between current row and unbounded following) /
         sum(cola) over (order by dt rows between current row and unbounded following) kpi;
from t;

+------------+------+------+---------+---------+--------+
| dt         | cola | colb | sumcola | sumcolb | kpi    |
+------------+------+------+---------+---------+--------+
| 2020-10-29 |  147 |  150 |     847 |     830 | 0.9799 |
| 2020-10-30 |  160 |  170 |     700 |     680 | 0.9714 |
| 2020-11-01 |  170 |  170 |     540 |     510 | 0.9444 |
| 2020-11-02 |  180 |  170 |     370 |     340 | 0.9189 |
| 2020-11-03 |  190 |  170 |     190 |     170 | 0.8947 |
+------------+------+------+---------+---------+--------+
5 rows in set (0.001 sec)